Skip to content

Instantly share code, notes, and snippets.

@danwagnerco
Last active January 28, 2016 20:37
Show Gist options
  • Save danwagnerco/73a6a95cc22f8faaa87a to your computer and use it in GitHub Desktop.
Save danwagnerco/73a6a95cc22f8faaa87a to your computer and use it in GitHub Desktop.
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 "Invoice.zip" 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 "Invoice.zip"
.AutoFilter Field:=1, _
Criteria1:="Thumbs.db", _
Operator:=xlOr, _
Criteria2:="Invoice.zip"
'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
.ShowAllData
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