The CTRL+END Shortcut Goes Too Far in Excel – 6 Solutions

Consider the list of clients in the dataset below. It showcases Company Names, their Addresses, the location City, and the Zip Code.

excel ctrl end goes too far

 

Solution 1 – Using the Properties Feature to Stop CTRL+END from Going Too Far

  • Go to the Developer tab >> select Properties.

Using Properties Option to Stop CTRL+END Going too Far

 

  • Click Categorized > in the ScrollArea field, enter the range. Here, $A$1:$F$14.

Note: Provide an Absolute Cell Reference by typing in the $ sign.

Selecting Scroll Area

  • Close the Properties window and press CTRL+END to go to the end of the dataset.

The scrolling area is limited to the data range.

Using Properties Option


Solution 2 – Deleting Content from Other Cells to Stop the CTRL+END from Going Too Far

There is text in B1048563.

Deleting Contents of the Other Cells to Stop CTRL+END Going too Far

  • Click the adjacent column as shown below. Here, Column F.

  • Press CTRL+SHIFT+Right Arrow key to select all cells >> Press CTRL+-(Minus) to delete the content.

Selecting Columns

  • Go to the row below the dataset and select it. Here, Row 15.

Selecting Rows

  • Press CTRL+SHIFT+Down Arrow key to select all cells >> Press CTRL+-(Minus) to delete content.

Deleting Contents

  • Press CTRL+END to move to the end of the dataset.

Deleting Contents of the Other Cells


Solution 3 – Utilizing the Immediate Window Feature to Prevent CTRL+END from Going Too Far

  • Go to the Developer tab >> click Visual Basic.

Utilizing Immediate Window Feature to Stop CTRL+END Going too Far

In the Visual Basic Editor window:

  • Click the View tab >> Select Immediate Window.

Open Immediate Window

 

  • Copy and paste this command into the window and press ENTER.
ActiveSheet.UsedRange

Inserting COde Into Immediate Window

  • Close the Visual Basic Editor >> press CTRL+END key and go to the last cell of your dataset.

Utilizing Immediate Window Feature to Stop CTRL+END Going too Far


Solution 4 – Hiding Rows and Columns to Stop CTRL+END from Going Too Far

  • Go to the column at the end of the dataset >> left-click to select it. Here, Column G.

Hiding Rows and Columns to Stop CTRL+END Going too Far

  • Press  CTRL+SHIFT+Right Arrow key to select all cells >> Right-click and choose Hide.

Hiding Columns

This will hide all the cells to the right of the dataset.

  • Go to the row below the dataset and select it. Here, Row 16.

Selecting Rows

  • Press CTRL+SHIFT+Down Arrow key to select the cells with data below >> Right-click and choose Hide.

Hiding Rows

This is the output.

Hiding Rows and Columns to Stop CTRL+END Going too Far


Solution 5 – Fixing the Range of Cells with a VBA Code

  • Go to the Developer tab >> click Visual Basic.

Fixing Range of Cells with VBA Code

In the Visual Basic Editor :

  • Select Insert >> choose Module.

Inserting Module

  • Copy the code and paste it into the window.
Sub Reset_Last_Cell()
    Dim x As Long
    x = ActiveSheet.UsedRange.Rows.Count
End Sub

The Sheets.Select method activates the selected sheet (here, Fixing Range of Cells with VBA). The ActiveSheet.ScrollArea property sets the scroll area to the specified range of cells (A1:F14).

Inserting VBA Code

  • Close the VBA window >> in the Developer tab, click Macros.

Fixing Range of Cells with VBA Code

In the Macros dialog box:

  • Select Fix_Dataset_Range Macro >> Click Run.

Fixing Range of Cells with VBA Code

Press CTRL+END to go to the end of the dataset. The scrolling area is limited.

Fixing Range of Cells with VBA Code


Solution 6 – Applying a VBA Code to Stop CTRL+END from Going Too Far

  • Follow the steps in Solution 5 to open the VBA window, insert a new Module, and enter the VBA code.
Sub Fix_Dataset_Range()
Sheets("Fixing Range of Cells with VBA").Select
ActiveSheet.ScrollArea = "A1:F14"
End Sub

Define the variable x and assign the Long data type. Use the UsedRange property to count the number of rows and columns within the used range.

Applying VBA Code to Stop CTRL+END Going too Far

  • Close the VBA window >> click Macros.

Applying VBA Code to Stop CTRL+END Going too Far

In the Macros dialog box:

  • Select the Reset_Last_Cell Macro >> Click Run.

Applying VBA Code to Stop CTRL+END Going too Far

You can move to the last cell of the dataset:

Applying VBA Code to Stop CTRL+END Going too Far

 

Read More: How to Select Cells in Excel Using Keyboard


Download Practice Workbook

Download the practice workbook.


Related Articles


<< Go Back to Select Cells | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo