Forum Discussion
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.
Cells.Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$T$3295").AutoFilter Field:=1, Criteria1:="<>"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
When recording the macro each step works instantly. When running the macro, it works correctly but it takes 30 seconds to run. I suspect that the macro may be slow because of the line: “Rows("2:65536").Select” which runs the macro on all 65536 possible rows. The number of rows in the spreadsheet can vary. In this case the spreadsheet had about 4000 lines.
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
3 Replies
- 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
NextBut now my next step is to change the condition to only delete rows with the text "BAD", "UGLY" or "AWFUL" in the first columnLonnieCurrier 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 NextRiny_van_EekelenThanks again. That is exactly what I needed!
