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 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:

    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:

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! 🙂

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

Related posts: