Reset the used range

[Will Riley]

Over time, I've seen many a question about the annoyance of excel retaining the "old" used range settings when old data has been cleared from the worksheet.

You know what I mean..... you once had 10,000 rows of data and deleted/cleared 5,000 rows, yet XL still scrolls to row 10,000 when you try to go to the bottom of the datarange.... annoying yes??

This has been well documented around the XL world, but I don't think I've seen it here, other than in thread solutions....

Anyway, any reference to the used range via VBA will reset the value to the current used range.

So, set up a macro in your Personal.xls macro workbook & assign it a shortcut (for ease of use) and just use the following code....

Sub ResetRng() 
    ' Keyboard Shortcut: Ctrl+e 
    ' resets the used range of the activesheet 
    ActiveSheet.UsedRange 
End Sub 

Hope this helps,

Will

[parry]

Hi Will, yes I get this question a lot too. I notice that sometimes, and I cant find any consistent cause, Activesheet.UsedRange doesnt always reset the usedrange. However, by including the Application qualifier it always works...

Application.ActiveSheet.UsedRange 

[Richie(UK)]

... and another take on the issue:

http://www.j-walk.com/ss/excel/tips/tip73.htm

[parry]

Cheers thanks Richie. Hows things? You seem to be on every Excel board in the planet.

[Richie(UK)]

Hi parry,

I'm good, thanks. You?

Of course, you realise that in order for you to know that I seem to be on every Excel board on the planet then you must also ...