Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
This macro deletes rows using the Range.AutoFilter method (instead of a For loop)
Option Explicit
Public Sub DeleteRowsWithAutofilter()
Dim wksData As Worksheet
Dim lngLastRow As Long
Dim rngData As Range
'Set references up-front
Set wksData = ThisWorkbook.Worksheets("data")
'Identify the last row and use that info to set up the Range
With wksData
lngLastRow = .Range("F" & .Rows.Count).End(xlUp).Row
Set rngData = .Range("F5:F" & lngLastRow)
End With
'Here is where we use the .AutoFilter method to crush those
'annoying "Thumbs.db" and "" rows
Application.DisplayAlerts = False
With rngData
'Apply the Autofilter method to the first column of
'the range, using xlOr to select either
'"Thumbs.db" or ""
.AutoFilter Field:=1, _
Criteria1:="Thumbs.db", _
Operator:=xlOr, _
'Delete the visible rows while keeping the header
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
End With
Application.DisplayAlerts = True
'Turn off the AutoFilter
With wksData
.AutoFilterMode = False
If .FilterMode = True Then
End If
End With
'Let the user know the rows have been removed
MsgBox "Damn son! Rows removed."
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment