Excel file size - redundant range included in UsedRang

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
Hi All

How do redundant (for want of a better word) ranges contribute to file size, and why?

I have just built some code to remove redundant rows and columns from sheets in a workbook. I received a file of around 36MB, and once redundant rows and columns were removed the file size was reduced to 3.2MB.

I know what causes Excel to consider redunant ranges within the UsedRange, but I don't know why and I don't know how these actually contribute to the overall size.

Example:
Sheet1 contains a pivot table. At one point the pivot table occupied A1:Z1000. The current occupied range is A1:H100.
The sheet UsedRange is A1:Z1000 and the file size is 5MB. I delete rows 101:1000 and columns I:Z; save the file and the size is reduced to 4MB.

How does the app allocate capacity to the redundant rows and columns?

Looking for a little background info. Explanations or references to good articles welcomed :)

Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top