excel - 更改代码以使用选择来填充数组

Question

我有一个工作代码，它可以做它应该做的事情。我只想更改myStrings变量的填充方式。

从：

myStrings = Array("Test23", "Test-12", "54-Test")

至：

myStrings = Selection.Value

如果我运行它，我会收到运行时错误 9；下标超出范围。

目标是选择一些带有文本的单元格，始终是同一列，只是不同的行，并且 VBA 宏应该从工作簿的每个工作表中删除所有带有该文本的行。

我是 VBA 的新手，我从不同的来源结合了这个，我不知道如何解决这个错误。

如果它做同样的事情，我很感激任何帮助，甚至可能是不同的解决方案。

谢谢你。

Option Explicit

Sub LoopThroughWorksheets()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range

     Set myRng = sh.Range("A:N")

     myStrings = Array("Test23", "Test-12", "54-Test")

With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

       With sh

        .Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView


        .DisplayPageBreaks = False

            With myRng

            For I = LBound(myStrings) To UBound(myStrings)
                Do
                    Set FoundCell = myRng.Find(What:=myStrings(I), _
                                               After:=.Cells(.Cells.Count), _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                    If FoundCell Is Nothing Then
                        Exit Do
                    Else
                        FoundCell.EntireRow.Delete
                    End If
                Loop
            Next I

        End With

    End With

With Application
    .ScreenUpdating = True
    .Calculation = calcmode
End With

    ActiveWindow.View = ViewMode

Next sh

MsgBox ("Completed")

End Sub

标签： excelvba