Delete row if match found

[Harshil Mehta]

I want to delete rows if the code finds a match from the list in another sheet.

The below code does not delete all the rows where the match is found in one go.

Sub Delete_Newlyadded()
    
   Dim Cl As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Definition.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Value
        Next Cl
    End With
    With Sheets("New.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.EntireRow.Delete
               
            End If
        Next Cl
    End With
            
End Sub

 

[Fluff]

How about

Sub Delete_Newlyadded()
    
   Dim Cl As Range, Rng As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Definition.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Value
        Next Cl
    End With
    With Sheets("New.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
               
            End If
        Next Cl
    End With
    If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Harshil Mehta]

How about

Sub Delete_Newlyadded()
   
   Dim Cl As Range, Rng As Range
    Dim Dic As Object
   
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Definition.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Value
        Next Cl
    End With
    With Sheets("New.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
              
            End If
        Next Cl
    End With
    If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

This is great. Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback