Delete all unused blank rows at the bottom in excel 2013

Do you mind to attach your file here? I think the MS website solutions should solve your problem.

Are you trying to hide the unused rows? If so, click on row no. 600 (row header) --> This will select the whole row 600 --> Now hold down the Ctrl+Shift+Down Arrow Key --> Right click --> Hide.

Is this what you are trying to achieve?

The key is to delete all rows and then Save the workbook

So
- Select row 157 (or the first empty row you want to delete after your data)
- ctrl+shift+Down and then press down
- All empty rows should be selected (highlighted)
- Right click and delete all the rows
- Save your workbook

Once you delete these rows (and Save the File!) the file size should reduce assuming there was a lot of content in these rows before deleting. Even though, you've deleted the Rows you will still be able to see them on the spreadsheet (unless you hide them!), but they will be empty.

I sense that you probably don't want Rows 600 onwards and also don't want to see them?? Then you need to Delete--Save--Hide in that order

A check would be to do a Ctrl+End (after deleting and saving). This should not take your active cell to beyond the Rows you've just deleted (row 600 in your case)

Hope this helps!

There is an excess format cleaner add in for Excel available for download at http://support.microsoft.com/kb/244435

This deletes excess formatting and resets the last cell for your data.

What did you do that didn't work ... message #8?

Did you try the format cleaner that is available for download from Microsoft free of charge?

How large your file is?
Can you zip your file and attache here to see that what can be done to resolve your issue?
Remember to desensitize the data.

Something that can really mess things up is having a cell way past the "data range" with a space in it. Deleting rows and or columns will not reset the end mark of the data range.

I have successfully reset the last data point for all the worksheets. They were previously set at the bottom right of the Excel worksheet some 2000 rows right and a million + rows down.

I saved the book as XLSB and it shaved off a couple of MB in the process.

You can get your file at

https://www.dropbox.com/s/aoqcqvnhyi...14-1.xlsb?dl=0

I hope that this works....never done by me before.

Thank you for the feedback.

I downloaded your file and then loaded it into Excel. It took about 2 minutes. I then determined where the end point of your data was (Ctrl + End) and found that it was the last cell on all the worksheets.

I have the Excess Format Cleaner from Microsoft installed and I ran it. I of course got the "Not Responding" message but it was just working. A couple of minutes later, it was finished. I saved the file as an XLSB closed it, rebooted my computer to be sure that there was nothing left in memory and reloaded the file and it loaded quite quickly.

That was all there was to it.

The XLSB file was a little more than 2 MB smaller than the XLSX file.

I have tested the Microsoft Add-in by setting the end point of data in a file for experiments at a spot far removed from the data by entering a space into a cell. I then tried to reset the end point by deleting all the rows from the bottom of the data in the worksheet to the bottom of the worksheet and repeated with the columns....that did not reset the end point. I selected all the area, columns and rows except the real data and used Clear All from the Editing Group on the Home tab....that did nothing. Finally, I ran the Format cleaner and it immediately reset the end point of the data correctly and the cell with the space had the space removed at the same time.

I have found this to be a very useful addition to Excel.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I suggest that you start a new thread for this as it is a new problem and is in a thread marked as solved. That way you will get more traffic to the problem and a much better chance of success.

I'm not exactly sure what it is that you are asking so this would be a good opportunity to create and example with a good explanation of what you are looking for.

I look forward to seeing your new thread.

Happy New Year to you and your family.

I must say that I have never seen that particular error before. It very much sounds like the data endpoint is at either the bottom of the worksheet or at the very right side.

I suggest that you start a new thread, upload your problem workbook. Send me a link to the thread and I will try and help you out.

It is too late here tonight so will look for further information from you tomorrow or when you can respond.