Imagine one of the most trivial tasks in Excel – remove rows from Excel, for which contain the number 1 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:
|
1 2 3 4 5 6 |
For cnt = 1 To lastRow Set myCell = ws.Cells(cnt, 1) If myCell = 1 Then myCell.EntireRow.Delete End If Next cnt |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
Option Explicit Sub RemoveRows() Dim rowsToDelete As Range Dim cnt As Long Dim currentlastRow As Long Dim ws As Worksheet Dim myCell As Range Set ws = Worksheets(1) currentlastRow = lastRow For cnt = 1 To currentlastRow Set myCell = ws.Cells(cnt, 1) If myCell = 1 Then If rowsToDelete Is Nothing Then Set rowsToDelete = myCell.EntireRow Else Set rowsToDelete = Union(rowsToDelete, myCell.EntireRow) End If End If Next cnt If Not rowsToDelete Is Nothing Then rowsToDelete.Delete End If End Sub Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long Dim ws As Worksheet If wsName = vbNullString Then Set ws = ActiveSheet Else Set ws = Worksheets(wsName) End If lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row End Function |
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! 🙂