VBA, Loop through sheets not detecting parameters?

Question

So I'm trying to format to all sheets apart from the "Names" sheet. and what I came up with below doesn't seem to be able to loop and detect the sheet "Names". It will try to format "Names" the said sheet is active or it will only apply format a single other sheets when the sheets is active

Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Names" Then
            Rows("1:1").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$1:$Q$19").AutoFilter Field:=4, Criteria1:="="
            Rows("2:2").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Delete Shift:=xlUp
            ActiveSheet.Range("$A$1:$Q$16").AutoFilter Field:=4
            Columns("G:G").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlToLeft
            Range("J15").Select
        End If
        
    Next ws

I've tried rewriting the codes completely but the same problem persists

You have to use your `ws` variable instead of `AchtiveSheet`. Maybe reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) will help you also — Ike, Jan 23 '23 at 16:48
Are you also trying to delete all the rows where you find the `Criteria1:="="` ? Since you'll quickly run into an issue where you'll have nothing to delete. — Notus_Panda, Jan 23 '23 at 17:06

score 0 · Answer 1 · answered Jan 23 '23 at 18:30

In addition to removing Activesheet, rewriting to avoid .select, and maybe considering an alternative to Criteria1:="=" (as already mentioned);
Consider using a With statement to definitely connect each action to the current sheet.

Sub Format_Worksheets()

    Dim WS As Worksheet
    Dim lRow As Long
    Dim lCol As Long
    
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Names" Then
            With WS
                .Rows("1:1").AutoFilter
                .Range("$A$1:$Q$19").AutoFilter Field:=4, Criteria1:="="
                lRow = .Range("A2").End(xlDown).Row
                lCol = .Range("A2").End(xlToRight).Column
                .Range(.Cells(lRow, 1), .Cells(lRow, lCol)).Delete shift:=xlUp
                .Range("$A$1:$Q$16").AutoFilter Field:=4
                lCol = .Range("G1").End(xlToRight).Column
                .Range("G1", .Cells(1, lCol)).Delete shift:=xlToLeft
            End With
        End If
    Next WS

End Sub

Let me know if this works out for you. It did for me... but I'm not 100% sure the formatting will match what your did. I rewrote it without .select or .activate but sometimes it's hard to tell without looking at the data.

Aren't you only deleting the last row there? `.Range(.Cells(lRow,1), .Cells(lRow, lCol))...` and then only the column headers of G and onward `.Range("G1", .Cells(1, lCol))`. Not sure the OP was trying to accomplish just one line. More the visible rows 2 and further and after getting rid of the filter, then the columns G and further. — Notus_Panda, Jan 24 '23 at 08:02

VBA, Loop through sheets not detecting parameters?

1 Answers1