Excel VBA Macro - Loop Through Sheets & Clear Rows

Question

I am trying to get this macro to loop through all sheets within a workbook & then filter & clear some cells.. the loop works when I do not have the filter/clear cells part of the macro enabled however as soon as I uncomment the filter & clear part of the macro, the macro will only filter and clear the cells within the Active Sheet, any ideas where I am going wrong?

Any help very much appreciated!

Sub MovethroughWB()
    Dim ws As Worksheet
    For Each ws In Sheets 'This statement starts the loop
        If ws.Name <> "Exclusions" Then 'Exclude this sheet from the loop
            Range("D2:J2").Select
            Selection.AutoFilter
            ActiveSheet.Range("$D$2:$J$200").AutoFilter Field:=7, Criteria1:="<>#N/A", _
                Operator:=xlAnd
            Range("D3:J200").Select
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.ClearContents
        End If
    Next ws
End Sub

You are using `ws` as a sheet variable for the loop, but then it is not used to qualify your `Range`s (so it assumes `ActiveSheet`) and then later you are explicitly using `ActiveSheet` instead of `ws` for some reason. — braX, Jul 16 '21 at 07:49
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jul 16 '21 at 07:53
I've tried to put "ws.Range("D2:J2").Select" but I just get "Select method of Range class failed" — Jonathan, Jul 16 '21 at 07:53
@Jonathan don't use `.Select` see the link I posted. work directly with the range like `ws.Range("D2:J2").AutoFilter`. Using `.Select` is a extremely bad pratice and comes with a lot of unwanted side effects. — Pᴇʜ, Jul 16 '21 at 07:54

score 1 · Accepted Answer · answered Jul 16 '21 at 07:58

Referce a worksheet for every object that is located in a worksheet (Range, Cells, Columns, Rows, etc)
Stop using .Select (How to avoid using Select in Excel VBA).

And your code gets reliable:

Sub MovethroughWB()
    Dim ws As Worksheet
    For Each ws In Sheets 'This statement starts the loop
        If ws.Name <> "Exclusions" Then 'Exclude this sheet from the loop
            ws.Range("D2:J2").AutoFilter
            ws.Range("$D$2:$J$200").AutoFilter Field:=7, Criteria1:="<>#N/A", Operator:=xlAnd
            ws.Range("D3:J200").SpecialCells(xlCellTypeVisible).ClearContents
        End If
    Next ws
End Sub

Excel VBA Macro - Loop Through Sheets & Clear Rows

1 Answers1