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?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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?
 
Joined
Jan 29, 2018
Messages
2
Reaction score
0
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.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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!
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top