The 'phantom cells' left after RemoveDuplicates is killing my Lookup speed. Any way to remove them without a workbook save?

[d0rian]

I use the code below to paste a single column of 61,000 cells to a new sheet and remove all duplicate values, leaving approx 3,400 unique values. When I press Ctrl+End after removing the duplicates, it takes me to Row 96,609 (which is kinda weird since only 61,000 cells were in the original range), but no problem, once I Save the workbook, Ctrl+End puts the cursor (correctly) in row 3,403, which is the last row of the unique values.

The problem, however, is that I have VBA that pastes a formula that runs a Index/Match binary search on the resulting (dedup'd) column....it runs in under a half second after I've saved the document BUT it takes nearly 30 seconds if I run it on the unique-value column before I've saved the document (which, I guess Excel still considers to have 61,000 used cells, even though I've already used RemoveDuplicates to remove all but the unique values.)

So my question is whether there's any way to get Excel to ignore the 'phantom cells' that are left after running the RemoveDuplicates function (but before I save the doc)...because that weirdly seems to make my Index/Match binary search run like 50x slower.

Sub new_2()
    Application.ScreenUpdating = False
    Range("dedup_colA").Clear

    Range("data_source").Copy

    With Sheets("dedup")
        .Range("dedup_colA").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range("dedup_colA").RemoveDuplicates Columns:=Array(1), Header:=xlNo
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

Any UsedRange operation on that sheet will reset the End of Sheet.
Try adding:-

ActiveSheet.UsedRange

Unfortunately it only works on the "Active" sheet.

 

[d0rian]

Any UsedRange operation on that sheet will reset the End of Sheet.
Try adding:-

ActiveSheet.UsedRange

Unfortunately it only works on the "Active" sheet.

Lifesaver!! Thank you!
(I just activate the sheet I need it on with screen-updating turned off, then re-activate the sheet i started on; adds a brief flicker to the screen but otherwise does the trick.)

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.