Excel Made Easy

Vba delete rows with vba in Excel

What is it with deleting a row in VBA? Is that too much difficult? Why is this a separate chapter?

Because, Deleting a row may be a simple task, but there is a difficulty.

So, If Row 5 has to be deleted from a sheet, it will be a simple one line code, say for example, Rows(5).Delete.

Consider a situation, where a row has to be deleted based on a value in a particular cell, say for example,

delete a row if there is "ABC" in Cell A5, and there are 40 rows in total.

The simplest and only way is to loop through A and once the value is found, delete that row, that is, Rows(5).Delete.

Here rises the issue, once Row(5) is deleted, the Row6 becomes, Row5 and if there is the same value there too, that is "ABC",

then, this row will not be deleted as it is Row5 and the program is on Row 6.

Hence, while deleting Rows in VBA, The loop should be from the last used Row and upwards.

By doing so, the the Rows does not shift after deleting and data is preserved.

The following example illustrates this:

The screenshot of the editor is shown below:

Note that, we have used Rows 19990 to 20000 just for illustration since it will not affect the layout of this sheet.

You can find similar Excel Questions and Answer hereunder

1) How can I avoid updates to cell values during macro execution?

2) How do I enter a formula in a cell using VBA (using Absolute Reference)?

3) Excel 2010 vba replacement of application filesearch in Excel

4) How do I assign a macro to a shape using VBA?

5) How to add a link in a sheet to another sheet

6) How can I get row count of filtered data?

7) How can I remove display of Gridlines in my worksheet using VBA?

8) How to use the trace error function in VBA

9) How can worksheet functions be accessed in VBA?

10) How can I show summary reports with Totals only hiding all the rows containing the details?