Ctrl-End doesn't bring me to the last cell [closed]

Question

Ctrl-end key is supposed to bring me to the bottom, right-most cell. However, on several cases, it goes beyond that, meaning it goes hundreds of cells down, even if there are actually no data on those cells.

Can you advise a VBA script on how to clean-up those possibly invisible data that causes this? I'm pretty sure it doesn't work with a regular delete as despite doing that, it still behaves the same way.

Answer 1

Ctrl-End does take you to the bottom-right cell, it just uses different rules to what you'd expect in deciding what that cell should be. Just about any cell that's been referenced in any way will be included in the decision. It's annoying, but that's how Excel "works".

Here's a description of how to address the problem.

To paraphrase:

1. Select the last cell that contains data in the worksheet

2. To delete any unused rows:

   • Move down one row from the last cell with data.
   • Hold the Ctrl and Shift keys, and press the Down Arrow key
   • Right-click in the selected cells, and, from the shortcut menu, choose Delete
   • Select Entire Row, click OK.

3. delete entire row

4. To delete any unused columns:

   • Move right one column from the last cell with data.
   • Hold the Ctrl and Shift keys, and press the Right Arrow key
   • Right-click in the selected cells, and, from the shortcut menu, choose Delete
   • Select Entire Column, click OK.

5. Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.

Answer 2

This script will reset the last used row for any sheet that is active when it is run.

Sub Reset()

    Dim lngCount As Long

    lngCount = ActiveSheet.UsedRange.Rows.Count

End Sub