May 18 2020 02:29 PM
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.
May 18 2020 11:02 PM
May 19 2020 12:01 AM
Solution@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
May 19 2020 04:45 AM
@Riny_van_EekelenThanks again. That is exactly what I needed!
May 19 2020 12:01 AM
Solution@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