CODEWITHSUNDEEP
vbaexcel
Paramasivan
Paramasivan

Reputation: 791

Delete rows from one sheet based on values from another sheet

I have total email ids in COL A of Sheet 1 and bounced email ids in COL A of Sheet 2. I want to delete Sheet 1 values or entire rows based on values on Sheet 2.

I tried the following code but doesn't work.

Public Sub delete_selected_rows()

'look at sheet2, A1 through A3 for search values
For Each search_value In Worksheets("Sheet2").Range("A1:A3")
'as long as there is something to delete...
  Do While Not Worksheets("Sheet1").Range("A1:A3"). _
    Find(search_value.Value, lookat:=xlWhole) Is Nothing
    '...delete that row
    Worksheets("Sheet1").Range("A1:A3").Find(search_value.Value, _
    lookat:=xlWhole).EntireRow.Delete
  Loop
Next

End Sub

Any help ?

Upvotes: 0

Views: 4911

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

I would use this one:

Public Sub delete_selected_rows()
    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow as Long

    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count,"A").End(xlUp).Row  
        Set rng1 = .Range("A1:A" & lastRow)
    End With

    Set rng2 = Worksheets("Sheet2").Range("A:A")

    For Each c In rng1
        If Not IsError(Application.Match(c.Value, rng2, 0)) Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c

    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub

Upvotes: 2

L42
L42

Reputation: 19727

Try this:

Sub Macro1()

Dim lrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

With ws1
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("B1:B" & lrow)
        .Formula = "=IFERROR(MATCH(A1," & ws2.Name & "!A:A,0),"""")"
        .Value = .Value
        .AutoFilter 1, "<>"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

End Sub

Upvotes: 1

Related Questions