Workbooks that the sizes don't make sense and won't support formulas

[mattxu2006]

hello Gentlepeople

I have been given workbooks that are use more memory than makes sense.

All are data; no formulas.

the smallest is a xlsx file of 173MB.

91308 rows, 35 Columns

If I make a file with the same number of rows and columns, the file size is 8.76MB
copying and pasting values in a new workbook does not help.

These files also have problems calculations:
I need to match two of these workbooks to see if they have the same value for the same ID
I tried doing a sumif down one worksheet and it will not calculate down to the bottom row. About a 3^rd of the way through, it just repeats the value down to the bottom.

I checked for non-printing characters. Only found 33.

Is there a way to check what may be happening in the background to make these workbooks so unwieldy?
Thanks

 

[Michael M]

You could run this bit of code to see if there is unwanted "bloat"
Run the code save >>Close>>reopen !!

Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[mattxu2006]

HI Michael
What is the intent of this macro? It appears that it would only activate each worksheet in the workbook.

 

[Michael M]

It ensures that each sheet is only using the actual Usedrange of data !
Not, as is sometimes the case, thousands of blank cells that are counted as data.
The code does not impact on any data, formulas, formatting etc.

 

[mattxu2006]

Hi Michael
Sorry for the slow reply. Weekends may do that.
The macro does not run.
I imagine that ActiveSheet.UsedRange is missing something?

 

[Michael M]

Where did you put the macro...works fine for me.
It may not appear to run, but it simply accesses each sheet and sets the usedrange to only that!!

 

[mattxu2006]

I found that this does not work in a module
I put it into Thisworkbook and it ran.
While it did not eliminate the bloat, it reduced the bloat a bit.

Thank you

 

[Michael M]

It won't reduce bloat if the usedranges are correct.....only CTRL + End takes you waaaay down the worksheet / s

 

[mattxu2006]

Anyone else have ideas? should I provide more information to help clarify my issue?
TIA
Matt

 

[Michael M]

I did note in your first post you said

All are data; no formulas.

Then you said

These files also have problems calculations:

So, are There formulas on the sheet??
Is there conditional formatting on the sheet?
Are there, tables, pivot tables or graphs ?

What cell does CTRL + End take you to....is it at the end of the actual data ?