How do I reclaim unused columns and rows?

[MPW]

Using Office 2003 in XP

I have looked at and tried several fixes for clearing unused cells but without any success.

I have selected the offending columns/rows and used the "Clear All" command from the menu.
Then right-clicked selected the "Delete" command.
Saved and closed.
After re-opening it CTRL+END still sends it to cell IV65536.

I tried several macros using code like:

x = sh.UsedRange.Rows.Count
y = sh.UsedRange.Columns.Count

No success there either.
Strange thing is that clearing and deleting the rows and columns worked on the 1st sheet but not on the 2nd sheet of the same workbook.

On a side note: my goal here is to reduce the overall size of the workbook. As much as I would like to start over, this workbook has many formulas and vba and has been developed for over 10 years. I am not confident in the copying/exporting process to try to bring over all the userforms etc.

Any help is greatly appreciated.

MPW

 

[jeffreybrown]

I've had a lot of success with

Sub makelastcell() from http://dmcritchie.mvps.org/excel/lastcell.htm

 

[MPW]

Thanks for the site. I went there a while back but never saw that particular routine. Unfortunately it failed as well. It did make me wonder about merged cells, numerical formatting etc, so I tried to change all that back to the defaults, but no go. I do hide and unhide those rows using code but that should not effect anything. I even copied the sheet to a new workbook and the macro still failed.

Thanks for taking the time, if you have any other ideas I am open to suggestions.

Mark

 

[jeffreybrown]

Hi Mark,

Only other thoughts:

Place your cursor in the last unused cell in column A (assuming it is the same through all columns) and select Ctrl + Shift + Right Arrow then Ctrl + Shift + Down Arrow.

Don't delete >> Clear all

Repeat this for the columns

Place your cursor in A1 or in the last used cell and then save & close

if this still does not work then maybe this thread could help...

http://www.mrexcel.com/forum/showthread.php?t=406930&highlight=lipo

 

[MPW]

Thanks Jeff,

Using the Clear All command did not work but the link that you sent me did work on the copy of the original. A while ago I was working on a similar answer but without using the copy/paste function. I was trying to duplicate the entire workbook into a new workbook by trapping the values of each cell, including cell color, formulas, conditional formatting, etc. I finally got stuck on the validation boxes. The reason I have been pursuing this is because of the hidden baggage attached to the workbooks. This baggage is not only in the sheets but even in the modules. My goal was a fresh start for older documents, code and all. Thanks again for the link.

Mark

 

[jeffreybrown]

Hi Mark,

Glad to hear some things are working out.

Here are a few other links you might find useful.

http://www.mrexcel.com/forum/showthread.php?t=250047&highlight=clean+workbook

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[MPW]

The CodeCleaner sounds interesting but I really would like something I can pick apart (not compiled). I still believe that by loading code into a variable, it can be safely transported into a new workbook without the baggage.

Thanks again,

 

[Festus Hagen]

Have you tried M$ KB: How to reset the last cell in Excel

-Enjoy
fh : )_~

 

[MPW]

Thanks for the link.
It did reduce the file size by about 400k. Every little bit helps, or should I say byte

CTRL+END still goes to IV65536 on the 2nd Sheet.