I have a huge spreadsheet that I need to slim down to only the data that I need. Column G is one of several different options (ABI, CCK, DEP, TSL, TSS, WTD or XFR). I only need to look at the data that contains CCK or DEP in column G. I found the macro below and tweaked it for my spreadsheet, however, when I run it...not everything is deleted out like it should be. It removes all of the columns with TSL, TSS and XFR, like it is supposed to, but it still leaves in the rows with ABI and WTD in column G. Any ideas?
Sub Delete_with_Autofilter_Array()
Dim rng As Range
Dim calcmode As Long
Dim myArr As Variant
Dim I As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
myArr = Array("ABI", "TSL", "TSS", "WTD", "XFR")
For I = LBound(myArr) To UBound(myArr)
With ActiveSheet
.AutoFilterMode = False
.Range("G1:G" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
Set rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next I
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
Bookmarks