File size increase from .xls to .xlsm

[LeggoMyEggo]

Hello all,

I have been encountering an error when working with my workbook in the new .xlsm format. When initially saved, it takes up about 20 mb of space. However, one of the next times I save it (having changed little except to run a macro that causes no similar problems as a .xls) the file is >200 mb in size.

I am able to fix this by saving it as a .xls under a different name and then reopening and resaving with a .xlsm extension, but this is happening too frequently to be practical (and the file will be a scheduled task, so this file size increase will become a problem...)

Are there any known glitches that cause this large increase in file size...?

My workbook has multiple Active-X controls, some non-activeX controls, an advanced filter, and about 28 automatically generated charts (with a 3-d effect and a border).

Thank you,
Alex

 

[RSXchin]

The macros can take up a bit of space. You might want to delete the charts on close

 

[LeggoMyEggo]

I can't do that, the charts are the purpose of the file.

In a .xls the file size is 50 mb, when I initially save it as a .xlsm it is 20, however it is somehow corrupted to take up >200mb if I save it as a .xlsm multiple times.


What is the cause of the corruption? Or at least, what are some common causes...?

Thanks,
Alex

 

[LeggoMyEggo]

I have continued to look into this, and when running a compatability check to save back to .xls, it states that in most of my worksheets, I am using more than the ~65000 row limit. I am not intending to, and I think this is the problem.

What are some ways that I could be unintentionally increasing the number of rows used in my worksheet, or how could I reduce them with vba to keep the file size from ballooning...?

I didn't have this problem in .xls, so what could have changed...?


Thank you,
Alex

 

[stefankemp]

Do you have any event-handling code in the workbook? It could be that somewhere there is code that is changing cells in all rows in a sheet?

 

[LeggoMyEggo]

I have just found the offending line of code...
The number of lines that I can scroll to jumps from ~60,000 to the 1 million max after this line:

    Worksheets("OtherWkSht").Range("J:J").SpecialCells(xlCellTypeVisible).Copy Worksheets(WkshtName).Range("A1:A1000")

With this, I am copying the results of an advanced filter. It is the destination worksheet that is growing in size. I orgininally had simply:

  Worksheets("OtherWkSht").Range("J:J").Copy Worksheets(WkshtName).Range("A:A")

(which had the same problem. the first code was just my attempt to fix it)

How can I execute the transfer of the filtered rows of data without creating so many extraneous lines on the destination worksheet...?

Thanks,
Alex

 

[RoryA]

Assuming no data below the range of interest:

Dim rngData as range
with Worksheets("OtherWkSht")
    set rngdata = .Range("J1", .cells(.rows.count, "J").End(xlup))
End With
rngdata.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets(WkshtName).Range("A1")

 

[LeggoMyEggo]

Fixed it!


I had previously found the lastrow of the source data worksheet, so I used that in conjunction with the SpecialCells property and it works well!

With Worksheets("HistoricalData")

    LastRow = .Cells(Rows.count, "A").End(xlUp).Row
    .Range(Cells(1, 10), Cells(LastRow, 10)).SpecialCells(xlCellTypeVisible).Copy Worksheets(WkshtName).Range("A1") 

End with

This fixes my file size issue as well!

Thank you,
Alex

 

[RoryA]

You need periods before both your Cells calls to qualify them with the worksheet.