vba Delete row if value doesn't exist in a range

[Magoosball]

I have 2 worksheets (Sheet1 and sheet2). I am trying to write a macro that will look for the cell value of Sheet1 A2 in sheet2 column C. If that value doesn't exist in sheet2 column c then delete row 2 on sheet1.
This needs to happen for all rows on sheet1. The amount of rows on sheet1 and sheet2 varies.

Thank you!

 

[Vinoth_Shob]

Function Highlighter()
Dim k As Long
Dim lrow As Long
lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lrow1 = Sheet2.Range("C" & Rows.Count).End(xlUp).Row


For d = 2 To lrow
On Error Resume Next
Sheet1.Range("B" & d).Value = WorksheetFunction.VLookup(Sheet1.Range("A" & d).Value, Sheet2.Range("C1:C1048576"), 1, 0)
Next
Sheet1.AutoFilterMode = False
Sheet1.Range("A1:b1048576").AutoFilter Field:=2, Criteria1:=""
Sheet1.Range("A2:B" & lrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Sheet1.AutoFilterMode = False
Sheet1.Range("b2:b" & lrow).ClearContents






End Function

 

[Fluff]

Another option

Sub DelRows()

   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets("Sheet2").Range("C2", Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   Rng.EntireRow.Delete
End Sub

 

[Magoosball]

Hi Fluff,

This solution works. I am getting an error whenever there is no rows to delete. I.E. every value exists on sheet 2. Is there a way around this?

 

[Fluff]

Yup, just change the last line to

   If Not Rng Is Nothing Then Rng.EntireRow.Delete