vba - 根据单元格值删除行

Question

我正在尝试在 Sheet2 中的 A 列中搜索 Sheet1 中 A1 的值。

如果存在，我想删除 Sheet2 中的整行。

如果它不存在，我想打开消息框。

这就是我所拥有的，但我正在努力实际删除该行:

Sub Delete_Rows()
Dim FindString As String
Dim Rng As Range
FindString = Sheets("Sheet1").Range("A1")
If Trim(FindString) <> "" Then
    With Sheets("Sheet2").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Rng Is Nothing Then
            'I can't figure out how to delete the row
        Else
            MsgBox "Not Found"
        End If
    End With
End If
End Sub

Answer 1

这是一个基于 THIS 的示例

你不需要循环。您可以使用 .Autofilter这比循环更快。

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim delRange As Range
    Dim lRow As Long
    Dim strSearch As String

    Set ws1 = Sheet1: Set ws2 = Sheet2

    strSearch = ws1.Range("A1").Value

    With ws2
        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=" & strSearch
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    If delRange Is Nothing Then
       MsgBox "Not Found"
    Else
        delRange.Delete
    End If
End Sub