Macros making my spreadsheets too large

[ZAPPER]

After using macros, I have noticed that some very small files (20-30kb) are now 2-3MB! There is very little data in my spreadsheet, so I am assuming that it has something to do with the Macros. Is there any way to compress my spreadsheet, or some other way to fix this?

 

[Gord Dibben]

ZAPPER

Most common reason for workbook bloat is that Excel thinks the "used range" in
sheets is larger than it has to be and what you think it is.

Select a sheet and do a CRTL + END to see what Excel thinks is end of data
range.

Could be your macros are making the "used range" on the sheets much larger
than it has to be.

For possible fixes, manual and VBA, see Debra Dalgleish's site.

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

Gord Dibben Excel MVP

 

[ZAPPER]

Thanks, Gord! That did the trick. If anyone else needs this fix, here it is...

To programatically reset the used range,

Sub DeleteUnused()


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