IF statement in VBA code failing for each next

Question

I am trying to delete filtered rows from an Excel workbook with multiple worksheets. I used a dynamic range because each sheet doesn't have the same number of rows.

I have tried to use the If statement to make the code loop through some specific sheets but it won't work. The code doesn't move to other sheets without ws.Activate.

Sub DeletAnalyst2()
    'Declare variables
    Dim ws As Worksheet, startcell As Range, lastrow As Long, lastcol As Long

    'Set Objects    
     Condition = Application.InputBox(prompt:="Please type the condition text:")

    For Each ws In ThisWorkbook.Worksheets
        ws.Activate

        If (ws.Name <> "Presentation") And (ws.Name <> "Sheet6") And (ws.Name <> "sheet11") And (ws.Name <> "PrefTracks") And (ws.Name <> "AnalystNeeds") And (ws.Name <> "Post-Preference") And (ws.Name <> "Post Preference Grid") Then
            Set startcell = Range("A1")
            'Find last row and column of cells
            lastrow = Cells(ws.Rows.Count, startcell.Column).End(xlUp).Row
            lastcol = Cells(startcell.Row, ws.Columns.Count).End(xlToLeft).Column
            'select dynamic range
            Range(startcell, Cells(lastrow, lastcol)).Select

            'AutoFilter technique
            'ws.Range(startcell, ws.Cells(lastrow, lastcol))
            Range(startcell, Cells(lastrow, lastcol)).AutoFilter Field:=1, Criteria1:=Condition
            'deleting filtered
            Selection.Offset(2).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
            ActiveSheet.ShowAllData
        End If
    Next
End Sub

I expect code to loop through some sheets but not all sheets.

`doesn't move to other sheets without ws.Activate.` - because you need to [qualify](https://stackoverflow.com/q/17733541/11683) each `Range` and `Cells` call with `ws`. — GSerg, Jul 26 '19 at 14:10
Ex: use `Set startcell = ws.Range("A1")` instead of `Set startcell = Range("A1")`. [Reading this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) may also be useful. — cybernetic.nomad, Jul 26 '19 at 14:19
When I do Set startcell = ws.range(A1) the codes throws errors at Range(startcell, Cells(lastrow, lastcol)).Select Hey Gserg, did you mean defining all the ranges for each worksheet? — Meera, Jul 26 '19 at 14:24
The if statement doesn't work for this code. Code is lopping through all sheets and that is not what I want. — Meera, Jul 26 '19 at 14:26
@Meera do you have same issue for my [Answer](https://stackoverflow.com/a/57221976/11167163) ? — TourEiffel, Jul 26 '19 at 14:27
@Meera You are supposed to add `ws` to every single `Range` and `Cells` call. Not just the outer `ws.Range(Cells, Cells)`, but `ws.Range(ws.Cells, ws.Cells)`. — GSerg, Jul 26 '19 at 14:32
@GSerg I added ws to the range but run into an error - lastrow = Cells(ws.Rows.Count, startcell.Column).End(xlUp).Row — Meera, Jul 26 '19 at 14:48
@GSerg this is where it fails ws.Range(startcell, ws.Cells(lastrow, lastcol)).Select. The error says select method of range class failed. — Meera, Jul 26 '19 at 14:57
@Meera That most likely means that [`ws` is not active](https://stackoverflow.com/q/3592881/11683). — GSerg, Jul 26 '19 at 15:04
@GSerg I activated the ws and run into an error at - Selection.Offset(2).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp. This is because the macro is working on a sheet that should not be included. — Meera, Jul 26 '19 at 15:09

score 1 · Answer 1 · answered Jul 26 '19 at 15:00

You need to qualify all calls to Range and Cells with the worksheet they belong to, otherwise you will be inadvertently referring to cells from the active sheet.

You do not need to Select anything either.

Sub DeletAnalyst2()
    'Declare variables
    Dim ws As Worksheet, startcell As Range, lastrow As Long, lastcol As Long
    Dim Condition As String

    'Set Objects
    Condition = Application.InputBox(prompt:="Please type the condition text:")

    For Each ws In ThisWorkbook.Worksheets
      Select Case ws.Name
      Case "Presentation", "Sheet6", "sheet11", "PrefTracks", "AnalystNeeds", "Post-Preference", "Post Preference Grid"
        'Do nothing
      Case Else

        With ws
          Set startcell = .Range("A1")

          'Find last row and column of cells
          lastrow = .Cells(.Rows.Count, startcell.Column).End(xlUp).Row
          lastcol = .Cells(startcell.Row, ws.Columns.Count).End(xlToLeft).Column

          'select dynamic range
          With .Range(startcell, .Cells(lastrow, lastcol))
            .AutoFilter Field:=1, Criteria1:=Condition
            .Offset(2).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
          End With

          .ShowAllData
        End With

      End Select
    Next
End Sub

@GSergThe code runs the first time but fails to loop through and throws an error at - Selection.Offset(2).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp — Meera, Jul 26 '19 at 15:08
the problem is without ws.activate the code does not loop through the various sheets. After I enter ws.activate, the code tends to loop through all sheets even though we have specified that those sheets be excluded.@GSerg — Meera, Jul 26 '19 at 15:19
@Meera You should not add `ws.activate` to this code. It works without it. — GSerg, Jul 26 '19 at 15:34
code is working just threw a runtime error '1004. No cells were found for some of the conditions. How do I avoid that error? — Meera, Jul 26 '19 at 15:49
@Meera That depends on where it happens and what you want to do when it happens. — GSerg, Jul 26 '19 at 15:55

IF statement in VBA code failing for each next

1 Answers1