Force Refresh of "Last" Cell of the Worksheet

Question

Pressing Ctrl+End in Excel takes you to the bottom-right-most cell of the worksheet.

If you delete the last rows or columns and save the workbook, this last cell gets updated, as well as the scrollbars.

I remember there was a one line VBA command that you could run that would do the update without having to save the workbook, but I can't remember what the command is - do you have any ideas?

Answer 1

I’ve found something that consistently works to delete those blank rows. You can tell when the “used range” excel is using is too big and is going to add extra blank rows when you use the scroll bar to the right and it goes beyond the last row of actual data when you scroll to the bottom. This will caused extra blank records to be added to the table when it is imported into SQL.

To get rid of them:

1. Select the entire first row under the last row of data. Hit Ctrl + Shift + Down Arrow to select all the blank rows.
2. From the Home Tab Select Clear and then Clear All from the Edit menu (picture of a whitish-grey eraser).
3. Right-click and select Delete.
4. Goto cell A1 first and then Save.
5. Press Ctrl + Home and then Ctrl + End (This should take you the correct last cell in the used range (above blank rows).
6. Click Save again.

Answer 2

Here is the answer:

Sub x()
    ActiveSheet.UsedRange
End Sub

Run this and the last cell will be reset.

Answer 3

When none of the above works try this.

Select the unused rows and change the row height.

Now delete the rows and save.

Bingo!

Answer 4

Here's what I did... since none of the above worked (this time that is, which is sad cause this code was running beautifully then all the sudden xlCellTypeLastCell totally failed me.) This will only work if you hardcode the first cell of the region you wanna grab the last cell of... for example I was pasting data tables into a sheet of 12 - 40 columns and 60-90 rows... but since it was a paste, it always started in cell A79...

Worksheets("Data_Import").Activate
Range("A79").CurrentRegion.Select
A = Selection.Rows.Count - 1
B = Selection.Columns.Count - 1
Selection.Offset(A, B).Resize(1, 1).Select
Set DataEnd = Selection

I feel sad to NOT use the cool special cells thing, but alas, if it doesn't work! then I just can't use it. :C

p.s. - you could also throw in a

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Select

Answer 5

This solution works for Excel 2013, but may also work for most recent versions of Excel:

• Choose the worksheet where you want to change the last cell, and delete any unused rows and columns
• Click on File - Options - Customize Ribbon
• Under Main Tabs, check the box next to "Developer", then click OK
• On the Developer ribbon that now appears, click Visual Basic
• In the upper-left corner, under Microsoft Excel Objects, click on the Sheet Name where you want to force a refresh of the worksheet's last cell
• In the menu, click on Run - Refresh, then close the Visual Basic Window

When you hit Ctrl + End, your last cell should now be refreshed.

Answer 6

Check out http://dmcritchie.mvps.org/excel/lastcell.htm#MakeLastCell, found the link from a similar question.

Far from the forgotten one liner, but did solved the problem for me.

Alternatively;

1. Turn on manual calculation (to preserve references).

2. create new sheet.

3. Copy cells and Name of old sheet.

Answer 7

For some reason the code ActiveSheet.UsedRange alone did not work for me on Excel 2016 64-bit to force Excel to reset the last used cell.

This does work for me. Hope this helps.

Rows(25:415).Clear   'deletes contents and formatting
ActiveSheet.UsedRange   'resets last used cell

Answer 8

I'm not sure why everyone is making it so complicated. Just press Ctrl + PgDn.