Forum Discussion

LonnieCurrier's avatar
LonnieCurrier
Copper Contributor
May 18, 2020
Solved

Deleting rows in a macro based on cell contents

I am wondering if there is a faster way to delete selected rows in a macro. I recorded this short macro using data filter to select all rows with nothing in the first column, then deletes them all. ...
excel
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 19, 2020

    LonnieCurrier Taking your request literally, try this:

        Dim lastRow As Object
    Dim i As Integer
    
    Set lastRow = Range("A1").CurrentRegion
    
    For i = lastRow.Rows.Count To 2 Step -1
        If lastRow.Cells(i, 1) = "BAD" _
        Or lastRow.Cells(i, 1) = "UGLY" _
        Or lastRow.Cells(i, 1) = "AWFUL" Then
        
            Cells(i, 1).EntireRow.Delete
            
        End If
    Next

     

LonnieCurrier's avatar
LonnieCurrier
Copper Contributor
May 19, 2020
I have found this code to work MUCH faster:
    Dim lastRow As Object
    Dim i As Integer
    Set lastRow = Range("A1").CurrentRegion
    For i = lastRow.Rows.Count To 2 Step -1
        If lastRow.Cells(i, 1) <> "" Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next
 
But now my next step is to change the condition to only delete rows with the text "BAD", "UGLY" or "AWFUL" in the first column

 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 19, 2020

LonnieCurrier Taking your request literally, try this:

    Dim lastRow As Object
    Dim i As Integer
    
    Set lastRow = Range("A1").CurrentRegion
    
    For i = lastRow.Rows.Count To 2 Step -1
        If lastRow.Cells(i, 1) = "BAD" _
        Or lastRow.Cells(i, 1) = "UGLY" _
        Or lastRow.Cells(i, 1) = "AWFUL" Then
        
            Cells(i, 1).EntireRow.Delete
            
        End If
    Next

 

Resources