Hi Ken!
Sometimes the size of a Excel file can blow out to several megabyte
with very little apparent change to the cell. The most common problem
relates to the last cell of each worksheet.
False End Row or Column of Data
Use:
Edit > GoTo > Special
Check "Last Cell"
OK
Does it take you a lot further than you should go? If, "Yes", then you
need to delete all rows below the "real" bottom of your data and all
columns to the right of the furthermost column used by your data.
For each sheet in turn:
To reset the last row:
Select cell A one row down from the last cell with data.
Press and continue to hold down Ctrl + Shift keys, and then press the Down Arrow key.
Right-click > Delete
Check "Entire Row"
OK.
To reset the last column:
Select Row 1 of the last column with data in it.
Press and continue to hold down Ctrl + Shift keys, and then press the Right Arrow key.
Right-click > Delete
Check "Entire Column"
OK.
Having checked / done each sheet, Save the file although with Excel
2000 and before you have to use:
Save, Close, Open, Save
A VBA subroutine that does this is:
Sub DeleteUnused()
'Debra Dalgleish and others have posted this:
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub
Bloated VBA Code
Apparently, VBA code can get bloated, although I've not seen this.
Rob Bovey's codecleaner cleans up some of the junk left over in the
VBA modules. You can find it here:
http://www.appspro.com/
Opened up Lotus 1-2-3 Files
Files created in Lotus can become quite large until they are saved as
xls files.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
