Delete rows with based on cell value

Question

I'm trying to search Column A in Sheet2 for the value of A1 in Sheet1.

If it exists, I'd like to delete the whole row in Sheet2.

If it doesn't exist, I'd like the message box to open.

Here's what I have, but I'm struggling with actually deleting the row:

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

`Rng.EntireRow.Delete` ... place this where you have the comment *'I can't figure out how to delete the row* — Scott Holtzman, Nov 09 '15 at 14:18

R3uK · Answer 1 · 2015-11-09T15:02:28.737

Here is the code to :

loop through all the values in Column A of Sheet1,
look for all matches (with FindNext method) in Column A of Sheet 2
and delete the rows that matches

Give it a try :

Sub test_user5472539()
Dim Ws1 As Worksheet, _
    Ws2 As Worksheet, _
    LastRow As Long, _
    FindString As String, _
    FirstAddress As String, _
    cF As Range

Set Ws1 = ActiveWorkbook.Sheets("Sheet1")
Set Ws2 = ActiveWorkbook.Sheets("Sheet2")
LastRow = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row

For i = 1 To LastRow
    FindString = Ws1.Range("A" & i)
    If Trim(FindString) <> "" Then
        Ws2.Range("A1").Activate
        With Ws2.Range("A:A")
            'First, define properly the Find method
            Set cF = .Find(What:=FindString, _
                        After:=.Cells(1, 1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)

            'If there is a result, keep looking with FindNext method
            If Not cF Is Nothing Then
                FirstAddress = cF.Address
                Do
                    cF.EntireRow.Delete
                    Set cF = .FindNext(cF)
                'Look until you find again the first result
                Loop While Not cF Is Nothing And cF.Address <> FirstAddress
            Else
                MsgBox "Not Found"
            End If
        End With
    Else
    End If
Next i

End Sub

score 0 · Answer 2 · edited May 23 '17 at 11:59

Here is an example based on THIS

You don't need to loop. You can use .Autofilter which is faster than looping.

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

Delete rows with based on cell value

2 Answers2