VBA – Delete / Remove rows from a specific worksheet in Excel

Imagine one of the most trivial tasks in Excel – remove rows from Excel, for which contain the number in their first column. Or in the column below, these are the rows, which are selected.

If you are familiar with the for loop and you have not had this task until now you would probably decide to start a loop from 1 to 15 and delete every row in the loop, where the first cell is corresponding to 1. This is a good idea, but it will not work quite ok. With a code like this one:

You would get a result like this one:

As you see, some rows are deleted, but not all of them. This is because once a row is deleted, Excel moves all rows with one up. Thus, if there are consecutive rows, the second one does not get deleted.  To solve this problem, you should think a bit smarter – you should loop reversely – e.g. from the 15th row to the first one. Then it will be ok to delete the row, which you have noticed.

Anyway, deleting rows every time is not the best solution for a big Excel file – it means that you should execute the “delete”operation a lot of times. And deleting in VBA / Excel is a slow operation. Thus, you should think of a way to execute the “delete” once at the end of the program.

 

 

 

 

Something like this:

In general, this is what the code does:

  • checks the last row in column A with the function lastRow;
  • for every cell in the first column, it checks whether it is equal to 1;
  • adds the cell to the range rowsToDelete through a Union() operator or through a Set(), it this is the first member of the range;
  • at the end, it deletes the range;

Enjoy it! 🙂

Tagged with: , , , ,