Excel used range has entire columns
I'm dealing with many Excel files, and often run into workbooks that are very large for the data they contain, or worksheets that are very slow when scrolling. On the way, I learnt this is often due to an excessively large UsedRange, and it's one of the first things I check on these occasions.
If I find that the worksheet does indeed have a large UsedRange (by pressing ctrl+end), there are some things I try. I always end each option by selecting cell A1, saving the Workbook, and then closing Excel. And then hopefully see (in file manager) that the file size is indeed reduced. If it's not, I open the file again, confirm that the used range is indeed as large at it was before, and try the next thing on the list.
Here are the things I try:
- I select the section of entire rows (or columns, or both consequetively) that is unused and rightclick -> delete. Does the trick in 95% of the cases.
- If it doesn't work, I double down and also 'clear all' (home tab -> clear -> clear all) the usused rows, and remove all conditional formatting from the entire sheet, followed by again deleting the unused rows.
- If I'm still unsuccessful, I go to VBA, do
ActiveSheet.UsedRangein the immediate window to force a 'recalculation' of the used range. If that doesn't work, I try again but after deleting the unused rows and/or columns.
Now I have a workbook on my hands, which does not reduce its size even after the last trick. There is an excessive number of rows (namely: all) used. I noticed from VBA that apparently entire columns are used, as ActiveSheet.UsedRange.Address has the value $A:$EM - no row numbers!
This is indeed new to me, so I tried some more extreme things:
- I removed all formatting from the sheet (clicking in top left and then home tab -> clear -> clear formats), as well as hyperlinks, and comments and notes (from same dropdown menu). Followed by a deleting of all unused rows.
- I did the same but after selecting the 'Normal' style for the entire sheet and unmerging all cells.
- I've unfrozen the panes (I'm desperate here people).
The sheet now looks more like a text file than a worksheet, but it's still 3MB big (for 42 x 142 cells), and the used range according to excel (and VBA) is still the same (or almost, it's $A:$EL now).
Are there any other things I can do?
Many thanks!
PS - I'm on Office 365 ProPlus, Build 12430.20184 Click-To-Run
Two additional comments that might be of interest:
A) I've made sure the problem is really this one sheet:
- I've deleted all other sheets from the file. (I checked for very hidden sheets.)
- I've deleted all named ranges in the workbook.
- There are no VBA modules or forms attached to the workbook.
- And, most convincingly: if I add a new worksheet with roughly the same amount of data, and delete the offending one, the file size drops to a few 10 kB.
B) And also, I'll mention one thing that does work: select what I think is the used range, copy it, and paste it onto a new (blank) worksheet. However, this has its own drawbacks which make me reluctant to using it. Namely, all references must be changed over to the new sheet. That includes (but might not be limited to...(and that's the main problem)):
- any references in cells on other worksheets. These can be found by 'search-and-replace' of "original_worksheet!" with "new_worksheet!";
- any use in formulas for named ranges;
- any use in conditional formatting;
- use in VBA code, depending on how the sheet is referenced.
Also, any objects (like charts, control elements (dropdown-boxes etc), and, most importantly: tables) on the original worksheet will be renamed, so the above points apply to those as well. In short, I don't want to go this route if I can possibly avoid it, because it's much work, and I have anxiety about not finding all references that need to be changed, and thereby screwing up my workbook when deleting the original worksheet. Also, I'm curious and (by now) invested in finding the actual cause.
52 Answers
Try creating a new blank sheet, selecting only those cells with content (ie, don't select all), paste this in the new sheet, then delete the old one, renaming the new one to match the old one if needed
I just spent a few hours chasing this down. Every one of the manual tricks - selecting, deleting, using the "Clear Formatting" menu command, deleting rows, using VBA to delete rows, telling VBA to recalculate UsedRange all failed. The file would not get smaller and the UsedRange was always the max (million) rows.
The key -- when you print the UsedRange.Address in VBA, there are no row numbers!
? activesheet.usedrange.address
$A:$AHSo there is no subset of the rows specified in UsedRange. Something is forcing the range to the entire sheet. In my case, that something was RowHeight.
This worked for me. Open VBA (Alt-F11) and use the Immediate mode window. Enter these one at a time:
? activesheet.name
[shows sheet name]
? activesheet.usedrange.address
$A:$AH
activesheet.usedrange.rowheight = 23
? activesheet.usedrange.address
$A$11:$AH$1493Not only does universally changing the rowheight allow the UsedRange to be smaller, it automatically recalculated UsedRange for me as part of the change!
Bingo, the file is now 2.5MB smaller.
2
