VBA Filter and Delete Rows Errors On Row Delete

[Shiseiji]

Hello all. The second code set is based this thread:

https://www.mrexcel.com/forum/excel-questions/553208-vba-filter-delete.html

though I've tried other suggstions and get the same result. An error on line 26.

Arggg . . . ideas

TIA

Ron

 1 Sub A5_tm_Filter() 2    'Application.DisplayAlerts = False
 3    'Application.DisplayStatusBar = False
 4    'Application.ScreenUpdating = False
 5'
 6    Dim thiswb                              As Workbook
 7    Dim thisws                              As Worksheet
 8    Dim LastRow                             As Long
 9    Dim lastCol                             As Integer
10    Dim rng                                 As Range
11'
12    Set thiswb = ActiveWorkbook
13    Set thisws = ActiveSheet
14    '
15    LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
16    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
17    '
18    If ActiveSheet.FilterMode = True Then
19        ActiveSheet.ShowAllData
20    End If
21'
22    Range("A1").Activate
23    With Range("A1", Cells(LastRow, lastCol))
24        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
25        .AutoFilter field:=2, Criteria1:="="
26        Range("A2" & Cells(LastRow, lastCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
27        .AutoFilter
28    End With
29    End Sub

 

[JLGWhiz]

Try substituting this

With Range("A1", Cells(LastRow, lastCol))
24        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
25        .AutoFilter field:=2, Criteria1:="="

With this

ActiveSheet.AutoFilterMode = False
With Range("A1", Cells(LastRow, lastCol))
.AutoFilter field:=2, Criteria1:="="

Also modify the next line to

Range("A2", Cells(LastRow, lastCol)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Shiseiji]

Thank you! And with much thanks to the years of others assistance on using this site, I can at least understand what you did though obviously not skilled enough to see the error the first time.

Ron

 

[JLGWhiz]

Thank you! And with much thanks to the years of others assistance on using this site, I can at least understand what you did though obviously not skilled enough to see the error the first time.

Ron

You're Welcome,
regards, JLG