VB to Cycle Through Worksheets; Use Filter, Delete Rows, then Next Worksheet

[Shiseiji]

Hi all. I'm back with hat in hand . . . I'm importing four worksheets then I need to cycle through them, apply the same filter and delete rows.
I have the filter working, but I'm not succeeding in moving on to the remaining worksheets. Once it's woking then I know the next step is an error handler to move to the next worksheet. Just in case . . .

TIA as always .

Sub m_NoID()
Dim ws As Worksheet
Dim thisws As Worksheet
Dim wb As Workbook
'
Set wb = ActiveWorkbook
Set thisws = ActiveSheet
'
    For Each ws In wb.Worksheets
        With thisws
            LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
            LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
            .AutoFilterMode = False
                If Not ActiveSheet.AutoFilterMode Then
                    ActiveSheet.Range("A1").AutoFilter
                End If
                   .Range("$A1", Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd
                    With .UsedRange.Offset(1).SpecialCells(xlCellTypeVisible)
                      If .Rows.Count > 0 Then
                         .Rows.Delete (xlShiftUp)
                      End If
                    End With
            .AutoFilter.ShowAllData
            Range("A1").Activate
            Range("A1").Select
         End With
      Next
End Sub

Ron

 

[Joe4]

You are setting "thisws" initially, then never touching it again. It will not automatically change as you loop through your sheets. You want to use "ws", something like this:

Sub m_NoID()

Dim ws As Worksheet
Dim wb As Workbook
Dim LastRow As Long
Dim LastCol As Long

Set wb = ActiveWorkbook

    For Each ws In wb.Worksheets
        With ws
            LastRow = .Cells(.Cells.Rows.Count, "B").End(xlUp).Row
            LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            .AutoFilterMode = False
                If Not .AutoFilterMode Then
                    .Range("A1").AutoFilter
                End If
                   .Range("$A1", .Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd
                    With .UsedRange.Offset(1).SpecialCells(xlCellTypeVisible)
                      If .Rows.Count > 0 Then
                         .Rows.Delete (xlShiftUp)
                      End If
                    End With
            .AutoFilter.ShowAllData
            .Range("A1").Select
         End With
      Next ws

End Sub

 

[Shiseiji]

Thanks for the quick reply. That is what I had 1st based on several posted code snippets, and it generated an out of range error. Then I had what I took as a "duh" moment and once I limited the code to the acitvesheet, that error was resolved. I'm getting the same out of range error with the code you posted. I also tried simply changing "Next" to "Next ws" but the code still only does the activesheet. Thoughts?

 

[Joe4]

See if this makes any difference:

Sub m_NoID()

Dim ws As Worksheet
Dim wb As Workbook
Dim LastRow As Long
Dim LastCol As Long

Set wb = ActiveWorkbook

    For Each ws In wb.Worksheets
        ws.Activate
            LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
            LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
            ws.AutoFilterMode = False
                If Not ws.AutoFilterMode Then
                    Range("A1").AutoFilter
                End If
                   Range("$A1", Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:="=", Operator:=xlAnd
                    With ws.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible)
                      If .Rows.Count > 0 Then
                         .Rows.Delete (xlShiftUp)
                      End If
                    End With
            ws.AutoFilter.ShowAllData
            Range("A1").Select
    Next ws

End Sub

 

[Shiseiji]

Runtime Error '1004'
"Autofilter method of Range class failed" Range("A1").AutoFilter

I also tried closing Excel completely. I don't remember the exact phrasing, but the idea was that sometimes the code will "hang" in some corner of Excel's memory . .

 

[Joe4]

Do you know if it is bombing out on the first sheet, or a later sheet?
If a later sheet, it may be a data issue that you are not accounting for.

 

[Shiseiji]

Thank you!. I added a blank worksheet, deleted all but it, renamed it Sheet1 in the properties, and saved the file. Opened the file and imported new data. Copied the worksheet 3 times. Success!
In the AH-64 there are as you can imagine 100s of thousands of lines of code. A million +, who knows. Well Peter Pilot writes up a gig, aircraft did XYZ. Altitude? Ambient air temperature? Fuel load? Weapons configuration? "Uh . . ." Tests are run, nothing. Then the maintenance test pilot takes the aircraft out for a test flight. Lets go turn JP8 into noise. "Could not duplicate. Aircraft returned to service." Ghost-trons . . . .

Thanks again.

Ron