Macro to clear cells in range that do not match any cell in another specified range? [pic example]

[Tardisgx]

https://imgur.com/a/OyeoFv9

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Jul38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("H4").CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4").CurrentRegion
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] Dn.ClearContents
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Tardisgx]

Hi mick I just tested it; it works on my example

Will it work on a sheet that reaches column DVO 16k rows down with data in each row occupying avarying horizontal # of cells.

Like my pic example occupied:

A:E
A:D
A:D
A:B
cells going across

Will it work the same for the bigger file described above?

 

[MickG]

Try changing the code similar to below :-
NB:- If the larger range has blanks it should not matter!!
NB:- might take a few seconds

[COLOR=#000080]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range("H4:H100") ' Change actual range to suit!!
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: .Item(Dn.Value) = Empty: [COLOR=navy]Next[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("A4:E16000") 'Change actual range to suit
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=navy]Then[/COLOR] Dn.ClearContents
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]

 

[Tardisgx]

Hi mick thanks for clarification.

I meant does the first cell in the above example Set Rng = Range("A4").CurrentRegion (I think you meant A3)

have to be the longest row going across? to set the range properly? I can test this myself when i get to my computer; still thought id ask. thanks

 

[MickG]

Possibly, The current region is a range bounded by any combination of blank rows and blank columns.
So by saying "A3" or "A4" may make no difference to the CurrentRegion Addess, it depend what you need to do to cover the Range you want to use.!!