VBA: Match Against a Blacklist and Delete Row??

[ONEILR2192]

Hello,

I'm trying to figure out a VBA solution to do the following:

If a cell in Sheet1 (my main sheet) range A7:A2007 is equal to any cell in Sheet2 (my blacklist) range A:A; Delete that entire row in Sheet1. (Both of these sheets are in the same workbook)

Because I'm already using some unrelated filters in the ranges specified I can't use a filter solution, I'm thinking it has to be a loop. I can't for the life of me figure out the language to accomplish this task. Hopefully some of the wizards around here can figure it out! PLEASE HELP!

Thanks!

 

[hiker95]

Adomynous,

Thanks for the workbook/worksheets, and, the additional worksheets to help understand your requirements.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Adomynous()
' hiker95, 11/19/2017, ME686091
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Range, fa As Range, lra As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  lra = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each a In .Range("A1:A" & lra)
    Set fa = w2.Columns(1).Find(a.Value, LookAt:=xlWhole)
    If Not fa Is Nothing Then
      a.Interior.Color = 65535
    Else
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      w2.Cells(nr, 1) = a.Value
      a.ClearContents
    End If
  Next a
  .Range("A1:A" & lra).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Adomynous macro.