Reduce the size of a sheet with empty space

[AMSancho]

Hello,
I have hundred of Excel workbooks with the same space problem.
In the news from 1998, I found this problem with Excel 95/98

-- Original problem (I'm experiencing)

Excel doesn't seem free up space (memory) when a large portion of a sheet
is deleted. Other than selecting the active cells and copying to another
sheet, how can I make Excel give up this space? -- Response
XL5, 95, 97 - Highlight all the unused rows and columns, using the row or
column labels. Select Edit>Delete... and save the workbook.
XL5, 95? - close and re-open the workbook.
To check if XL is actually 'seeing' *only* the actual extent of your data.
Highlight a cell in the used range and press Ctrl+End.

The question is:
Is there some possibility to clean the workbooks in an easier and
faster way?
Because making the cleaning by hand it is possible to delete necessary
information and cleaning some hundred of workbooks some of them with
decens of sheets is a long task.

Thanks in advance,
Angel

 

[Andy B]

Hi

I use a small macro that someone wrote (not me!!), with a button on my
toolbar to activate it.

Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

Hope this helps.

Andy.

 

[Otto Moehrbach]

Angel
Yes, you can automate this process but you have to provide Excel a means
of determining the last row and last column of your data range.
You cannot expect Excel to come up with this information by using the
UsedRange as the identifier of the range that you mean to be the used range.
That is the essence of the problem.
You will need to know what column has an entry in the last row of your
data, Also what row has an entry in the last column of your data. And you
will need this information for every sheet of every workbook or you will
have to provide some exclusionary criteria if you want some sheets/workbooks
to be skipped.
Post back with more detail about the layout of your data. HTH Otto

 

[Gord Dibben]

Debra Dalgleish has code to reset the Used Range on a sheet.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Gord Dibben Excel MVP

 

[AMSancho]

Thanks a lot Gord, with the macro on
http://www.contextures.on.ca/xlfaqApp.html#Unused
I could fastly to reduce the problem.
Regards,
Angel

 

[Otto Moehrbach]

Thanks Gord. I remembered that after I sent my response. I went into
Debra's site and put her code in my HowTo file. Thanks again. Otto