CODEWITHSUNDEEP
excelvba
Meera
Meera

Reputation: 11

IF statement in VBA code failing for each next

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.

Upvotes: 1

Views: 105

Answers (1)

GSerg
GSerg

Reputation: 78134

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

Upvotes: 1

Related Questions