How do I transfer large ranges into memory first, then do the comparison vs memory instead of by a cell range?

[Tardisgx]

The below macro clears cells that do not match with the D column range. The issue is my required ranges are too large for the macro to run on my computer.

Sub REMOVEPIDS()Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False


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


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

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

Set Rng = Range("F2:DVF62601") ' Range to clear

For Each Dn In Rng
If Not .exists(Dn.Value) Then Dn.ClearContents
Next Dn

End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

 

[Norie]

Why are you looping through all the cells in F2:DVF6260?

Couldn't you loop through the values/items in the dictionary and do a find/replace on that range?

 

[Tardisgx]

Cool thanks for your input; note to self don't mess with format when its working already; I also appreciate your attempts at my problem.

I had to stop your macro running when I got home today (the one i left overnight) over 12 hours although it did say I interrupted it;the macro seemed to have reached halfway down column D :/

My original macro with select special constants as the defined range completes the clearing process in eerily exactly 10 minutes; will be my go to selector for massive ranges.

Do I need to mark this thread solved somehow?

 

[Fluff]

Glad you got it sorted & thanks for the feedback.

No need to mark the thread as solved.