Can't Reset UsedRange

[jomili]

I have the codes below as macros in my Add-In. I use the FindUsedRange macro to determine what my used range actually is. I use the ResetUsedRng macro to reset the used range, but sometimes my Reset macro won't work, for instance with the attached workbook. Everything I've read indicates that it should, but after running Reset, saving, closing, and reopening, FindUsed tells me the used range is still what it was before, which is the wrong used range. I've tried copying the macro to the workbook and running it from there, and it still won't reset the used range. I'm at my wits end; how do I reset it?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mikerickson]

Does the workbook have any whole column formatting?
Empty cells with formatting other than Style Normal will increase the .UsedRange of a sheet.

I'm also a bit curious, if you have RealUsedRange, why concern yourself with Excel's UsedRange at all?

[jomili]

Because even though in VBA I can use RealUsedRange, my add-in is used by a lot of folks who aren't conversant with VBA, and for them all they want to do is reset their used range so using the END button has real meaning.

I don't think there's any whole-column formatting going on. The columns are all formatted as General.

[jomili]

Found a possible partial fix from the Contexture site (http://www.contextures.on.ca/xlfaqApp.html#Unused). I married the two macros Debra had there into one. However, the code won't work with merged cells, and it doesn't fix used range issues BEFORE the used range. For example, my example file had data starting in Row2 and Column2, so Row1 and Column1 are unused. The code doesn't delete them (which is good) but they ARE still counted in the UsedRange (which is NOT good).

Please Login or Register  to view this content.

[CaptMac]

If you end up here, click here. It's dumb from excel's point of view, but it worked for me.

[jomili]

Yep, I've had to do that many times.