Unable to delete empty rows and columns

Copper Contributor

I am unable to delete empty cells / rows / columns from my worksheet - I choose Delete and nothing happens. I have tried just cutting and pasting my data to a new document, but all of the empty cells go with it...

25 Replies

@G_Torres 

Select them, try

image.png

and click on Save.

@Sergei Baklan 

yes, that is what I have done - then nothing

@G_Torres 

Even with closing and reopening the file?

 

If Inquiry is available (check File->Options->COM Add-ins) you may try

image.png

@Sergei Baklan 

yes, I have saved, closed, then re-opened and then performed delete and they are still there. I don't have 'inquire' I am using Excel 2016 

@G_Torres 

Inquiry is for Pro versions and is not shown by default, you shall to activate it in Options.

Perhaps you may submit your file removing all sensitive information to play with it.

Hi @G_Torres,

I hope you got a solution already; this is for others who haven't yet. I tried everything possible and found online, but nothing could actually remove those unused but non resetable last cell in Excel sheet which made the sheet a lot (really lot!) bigger than it actually its. SO HERE IS THE WORK AROUND (might sound silly but this works; worked for me and made my Excel the optimum size:(

 

  1. Find the actual last used row in the sheet (with either a formula or a value). Select all the rows beneath it. Now comes the strange part - resize all the rows to 13 (or any other size), then back to 12.75 (standard Row height) and now try deleting (right click, then delete) the selection.
  2. Do the same for the last used column - select all the columns to the right of actual used column - resize it to 9 (or any random size), then to 8.43 (standard Column width) and now delete the selection.
  3. Now, press CTRL+END to check the last used CELL. If nothing changes, SAVE the file and REOPEN it to check the last used CELL again. This worked for me and nothing else did!

CHEERS!!!

 

 

I will try it, thanks!

Bro it does not work for me -_-@Amjeth 

@Amjeth 

That did the trick after a few hours trying other solutions I tried your solution that worked for me. 

File size went down from 3 Mb to 365 kb, last cell is now on row 1000 as it should and scrollbar can be used. 

@Mahdy1234No luck after saving the file and reopening it?
Hope you did delete the selection by Right-Clicking and then selecting Delete in the pop-up menu.
Pressing the Delete key on the keyboard or Clearing the contents alone won't help.

Yes, the regular delete rows and column function doesn't work - the extra rows and columns remain @Amjeth 

None of the suggestions have worked unfortunately - saving, reopening, deleting again, nothing. I can't remove the formats and resave as data then clear and reopen - it will remove too much work that has been completed
What would be really needed is a list of events causing this. The most popular solution is to delete the rows and this solution is flooding search results hiding less popular source. I came across someone using kind of brute force by combining multiple methods, Example Using Open Office on a Linux box to clean an Excel file.
Would be nice to have Microsoft to provide THE list of events causing this.

@GeoLeo 

I too tried deleting the rows the regular way, but it did not work. Strangely, the problem was resolved when I resized the unused rows and columns to their standard size and then deleted it. Try saving the file after deleting them, then reopen again to check (CTRL + END) the last used cell.

@Amjeth I've tried that to no avail - but will continue to try. thanks for the tip!

 

@Amjeth  This worked! I have been fooling with this for months! How weird - but thank you~

@G_Torres Had the same issue and when I removed all my filters it worked.

@AmjethThis worked for me, but only after I saved the file (with a new filename of course ...) Thanks for the tip. Nothing else had worked, except for copying and pasting into a new worksheet.

That worked! What a strange workaround, and thanks for sharing!