Clear cells in Range that do not match a "Master List"

[Tardisgx]

Alpha Frogs's macro here helped me greatly; (in this scenario I have a list of invalid codes) it find and replaces cells that do match a list of cells (list of invalid codes). If I replace with nothing I'm essentially clearing the cells which is what I needed.

sub Substitutions()
    
    Dim rngData     As Range
    Dim rngLookup   As Range
    Dim Lookup      As Range
    
    With Sheets("Sheet1")
        Set rngData = .Range("A:B")
    End With
    
    With Sheets("Sheet1")
        Set rngLookup = .Range("D1", .Range("D" & Rows.Count).End(xlUp))
    End With
    
    For Each Lookup In rngLookup
        If Lookup.Value <> "" Then
            rngData.Replace What:=Lookup.Value, _
                            Replacement:=Lookup.Offset(0, 1).Value, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False
        End If
    Next Lookup
    
End Sub

The information I'm dealing with here are 9 character long numerical codes; potentially alphanumeric in the future.

Now I have a "Master List" of valid codes and want to clear cells that do not match this list. I'm talking about exact matches not partial ones.

Related- i'm trying to be resource conscious; I can't get my head around how to create a loop i've read examples but getting nowhere; how to improve the above?

 

[NoSparks]

You don't indicate which range is the "Master List" so might need to swap range sets.
Try this

Sub Clear_Stuff()

    Dim MasterList        As Range
    Dim rngClear          As Range
    Dim cel               As Range
    Dim foundcel          As Range
    Dim lr                As Long
    
    With Sheets("Sheet1")
        lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        Set MasterList = .Range("A1:B" & lr)
        Set rngClear = .Range("D1", .Range("D" & Rows.Count).End(xlUp))
    End With

    For Each cel In rngClear
        If cel.Value <> "" Then
            Set foundcel = MasterList.Find(What:=cel.Value, _
                                           LookIn:=xlValues, _
                                           LookAt:=xlWhole, _
                                           SearchOrder:=xlByRows, _
                                           SearchDirection:=xlNext, _
                                           MatchCase:=False)
            
            If foundcel Is Nothing Then cel.Value = ""
        End If
    Next cel
End Sub

 

[Tardisgx]

Hi I am sorry I pasted a version without the notes on. also would have been smarter if I included the actual ranges. Below is a version of the master list macro I have which cannot work on the large ranges stated. The master list is a list in column A and the range of cells to clear if they doesn't match is C1:DVC62600 workbook looks like this https://imgur.com/a/usunRgo

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub REMOVEINV()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Dim Rng As Range, Dn As Range
Set Rng = Range("A2:A35524") 'Range to match against

With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

For Each Dn In Rng: .Item(Dn.Value) = Empty: Next

Set Rng = Range("C1:DVC62600") ' Range to clear
For Each Dn In Rng
If Not .exists(Dn.Value) Then Dn.ClearContents
Next Dn
End With
End Sub</code>

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...vs-memory-instead-cell-range.html#post5101170

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).