Excel file size - redundant range included in UsedRang

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,807
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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

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
Top