Reset the end cell

[Joanna James]

I need information on  how to reset the end cell in Excel  365.  My current worksheet has 28K lines of data, but the end cell is at 1048576.  I've tried the obvious select all open rows outside my data, then clear and/or delete, then save workbook, close and reopen.  But, in this case it doesn't work.  Thanks.

[#2]

HI Joanna

Sorry I'm not quite understanding you (may just be me), what do you mean by resetting the last cell? Are you attempting to delete the cells you aren't using outside the 28k cell range?

Cheers

Damien

[#3]

Hi Joanna

Are you actually deleting the rows or just pressing delete on the keyboard?
Normally deleting the rows and saving / reopening fixes it

[#4]

I've seen this too and the best way I've found to consistently fix it is to copy the desired range and paste to a new sheet.

I am usually helping a co-worker with this and really don't know where they get this abnormality. Sometime selecting the Rows and Columns and using right-click + delete works, but not always.

For some reason, I never have the issue with any of my workbooks.

[#5]

I have a problem like this.

If you like to test this:

1. start a new empty sheet

2. type a text/number in cell P25

3. Ctrl HOME brings the cursor to A1

4. Ctrl END brings the cursor back to P25

...

5. delete the text in P25

6. Ctrl HOME brings you to A1

7. Ctrl END brings you to ... P25, .... but nothing's there anymore!

I found a article: "Locate and reset the last cell on a worksheet" in support.office.com 

But it dosn't work in my Excel 2016

[#6]

Hi Anders,

In your sample click Save after the step 5 or 6, with #7 you will stay on A1 then.

Above if you didn't change format of the cell(s) you emptied. If so, first Clear All as in support article, and after that Save.

Another way if you have Inquire in your version, apply Clear Excess Cell Formatting from the menu in the ribbon after that tab.

If not, alternative VBA macros exist.

Latest two are suitable if you have quite a lot of such cells, usually within the file with long history.

[#7]

I have tried to reset the last cell in a spreadsheet by selecting all of the rows below the last data row, to the bottom, selecting "clear all", saving, closing.  On reopen, the last cell is unchanged.  There was mention in the help of vba macros that can help.  Can you point me to that?

[#8]

Hi Eric, you need to delete the rows not just clear the cells.

[#9]

Hi Wyn and thanks.  I tried that as well but will try it again...

[#10]

If you are on Excel 2016 or on 2013 Pro Plus you may use built-in Inquire to clean the worksheet, you only need to activate it first https://support.office.com/en-us/article/turn-on-the-inquire-add-in-6bc668e2-f3c6-4729-8ce1-75ea20aa...

Not sure about previous versions.

[#11]

I can't see what version I have because the "about" is grayed out.  BTW I'm on a mac.

I looked at Add-ins, which is in the Insert and Tools menu, if you type "Add-ins" in the help bar.  It's grayed out.  

[#12]

Hi Eric,

Sorry, I have no idea how to sort that out on Mac - I'm on PC.

[#13]

Ok, thanks for your help.  Any thoughts on where to go with this?

[#14]

Perhaps someone here who has experience with Mac will answer. And you may ask on answers.microsoft.com

[#15]

Gotcha, thanks...

Eric