时间戳

首页 > 解决方案 > VBA 代码中的 IF 语句对于每个 next 都失败

excel - VBA 代码中的 IF 语句对于每个 next 都失败

问题描述

我正在尝试从具有多个工作表的 Excel 工作簿中删除过滤的行。我使用了动态范围，因为每张工作表的行数不同。

我试图使用该If语句使代码循环通过一些特定的工作表，但它不起作用。没有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

我希望代码循环遍历一些工作表，但不是所有工作表。

标签： excelvba

解决方案

您需要对所有调用以及它们所属的工作表进行限定，否则您将无意中引用活动工作表中的单元格。RangeCells

你也不需要任何Select东西。

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

推荐阅读