Excel Cannot delete empty cells/rows

Joined
Jan 29, 2018
Messages
2
Reaction score
0
I have a macro-loaded, full of cross-sheet reference workbook that is still a bit bigger than I would like it to be/feel it aught to be.
I also noticed that the scroll bar on the right of *one* of the worksheets indicates it scrolls a long way past the end of my filled cells in that sheet. So I naturally guessed that sheet might be the problem.

I hit Ctrl+End to find the last cell (N1048576 in this case) and selected all rows from there to the bottom of my data. I tried "clear all" and "delete rows", but in both cases, even when I save, close, and re-open excel the last active cell is still that one, and the document never gets any smaller.

Am I doing something wrong? Any idea what's going on here?
 
Welcome to the forum :)

You mention that you have many cross-sheet references - are there any cells in other sheets that link to those cells you are trying to delete?
 
Welcome to the forum :)

You mention that you have many cross-sheet references - are there any cells in other sheets that link to those cells you are trying to delete?
Nope. No references whatsoever.
Checked with the arrows and everything.
 
Ok, instead of deleting the rows it might be worth a try to Clear them instead. Select the relevant rows, then under the Editing section you should see an option to 'Clear' (the icon is a pink eraser). Select Clear All then save your document. If you close it and reopen it then you shouldn't have excess rows in that sheet any longer. Let me know how you get on!
 
The same problem I encountered , my file size got extraordinarily big. Luckily i got this macro and it reduced my file size wonderfully.

sub abridge()

Sheets("OT_Rates").Select
Range("A1").Select 'you might want to change this , so make appropriate changes
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select 'this is where your desired delete range starts
Range("A" & ActiveCell.Row, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
ActiveSheet.UsedRange
ActiveCell.SpecialCells(xlLastCell).Activate

endsub
 
Back
Top