Excel file size excessively large (speed issues) -- Used Range is entire columns vs cell range

silverbullet2207

New Member
Joined
Nov 30, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I'm currently trying to build out a somewhat automated worksheet where questions are answered in cells on a tab, that drives row/column visibility on the same tab, as well as subsequent tabs using VBA. I've been working on this over the past several weeks and at some point, the file size went from approx. 200-400 kb to 2.9 mb, then later, to 5.6 mb.

After exploring some google results, I came across users having a similar issue due to the "Used range" of tabs using all 10 million rows. After performing a query to find out the used range on 2 of my tabs, I saw that the range was using entire columns as the used range (example columns A:H).

I've tried selecting all rows below my data and deleting the rows out entirely several different ways (using delete sheet rows from the "cells" area, using "ctrl" and "+", and right clicking next to all of the selected rows and clicking delete), to no avail. I'm not sure

I haven't been able to find a way to manually reset the "used range", but I'm fairly certain this is the cause of my speed/size issue.

Additional information: I determined the used range was using entire columns by going to the VBA editor and typing "?activesheet.usedrange.address" in the immediate window (opened using ctrl + G.

Thanks for any help/ideas in advanced, please let me know if any additional info is needed.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I've tried selecting all rows below my data and deleting the rows out entirely several different ways (using delete sheet rows from the "cells" area, using "ctrl" and "+", and right clicking next to all of the selected rows and clicking delete), to no avail. I'm not sure
you didn't say what size is after that and save?
 

silverbullet2207

New Member
Joined
Nov 30, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
you didn't say what size is after that and save?
There was no change to the size of the worksheet (on one attempt, it may be been inconsequential, maybe ~10 kb, probably a result of a different change made) and the used range didn't change from $A:$H
 

silverbullet2207

New Member
Joined
Nov 30, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It is possible to share this file here?

It's related to a work project, so I'd prefer not to. But should be able to provide screenshots or other information if needed.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
but it's about the size of a specific file, not an example, unless you create an example file that behaves the same, (which reflect structure, formatting etc.. of original file), eg. 300 kb to ca. 6mb
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Not sure of your sheet set up but U could copy and paste your data to new sheets and get rid of the old sheets... better yet stop using used range and be specific. U can also trial zipping and unzipping the file. Good luck. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,130,219
Messages
5,640,957
Members
417,183
Latest member
CuteLeo

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