File Size gets REAL big with Macros

[Mike Cross]

Can anyone tell me why my file which was about 4MB in
size grew to like 7MB by basically writing one macro -
see Macro below?

The Macro takes a list of data in the workbook and copies
the formula down for approx 8000 rows, then copies and
pastes a value that was calculated by these formulas,
sorts it (it is basically a query on a list of data based
on a users selection) then it deletes the formula again
(This pasting and deleting of the formula was an attempt
to shrink the file size)

Thanks in advance for your repsonse!

Regards,

Mike

Application.ScreenUpdating = False
Range("S5:V5").Select
Selection.Copy
Range("S6:V8000").Select
ActiveSheet.Paste
Range("V5:W8000").Select
Selection.Copy
Range("g17").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
If ActiveSheet.Protection.AllowSorting = False Then
ActiveSheet.Protect AllowSorting:=True
End If
Selection.Sort Key1:=Range("g17"),
Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("S6:V8000").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G10000").Select
Application.ScreenUpdating = True
End Sub

 

[pfsardella]

It sounds as though you've increased the size of your UsedRange on
your worksheet. It's not the code itself. It's what you've done to the
worksheet when you've copied ~8,000 rows.

See this link for how to test the UsedRange. Debra details how to test
the UsedRnage and what to do if the UsedRange extends well beyond your
actual data.

Oterwise, you just have a huge amount of space taken up by the
worksheet.

http://www.contextures.com/xlfaqApp.html#Unused

HTH
Paul