Delete rows from one sheet based on values from another sheet

Question

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 ?

Dmitry Pavliv · Accepted Answer · 2014-04-08 16:46:09Z

2

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

edited Apr 8, 2014 at 16:46

answered Apr 8, 2014 at 15:52

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paramasivan Over a year ago

Still it is not deleting from Sheet 1.

Dmitry Pavliv Over a year ago

are you sure your values indeed equals? code works for me perfectly..check your values for additional leading/trailing spaces

Paramasivan Over a year ago

There are no leading or trailing spaces. The values exactly match. But still it is not deleting in Sheet 1.

Cor_Blimey Over a year ago

ah, Simoco, I think rng1 and rng2 are mixed up. It should be searching for the values from Sheet2 in Sheet1, so the for each should be on rng2 and the .Match on rng1. Best approach otherwise imo and should run fast. @Paramasivan imo a comment like "It is not working" does not really help without providing more information e.g. stepping through the code in debug mode and describing what was/was not happening.

L42 · Accepted Answer · 2014-04-08 16:01:43Z

1

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

answered Apr 8, 2014 at 16:01

L42

19.8k11 gold badges47 silver badges70 bronze badges

Collectives™ on Stack Overflow

Delete rows from one sheet based on values from another sheet

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related