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.