Have you ever faced a situation where Excel’s CTRL+END shortcut goes too far and you are not sure how to fix it? Then, you’ve come to the right place. In this article, you’ll learn 6 proven fixes when an Excel CTRL END goes too far.
CTRL+END Shortcut Key Goes Too Far in Excel: 6 Possible SolutionsÂ
Let’s consider the list of clientele in the dataset shown in the B4:E14 cells. Here, we have the Company Names, their Addresses, the location City, and the Zip Code respectively. Therefore, without further delay let’s see each method individually.
Here, we’ve used Microsoft Excel 365 version, you can use any other version according to your convenience.
Solution-1: Using Properties Feature to Stop CTRL+END from Going Too Far
Let’s start with the simplest method to stop the CTRL+END shortcut from going too far. In this method, we’ll fix the scroll area just around the dataset. However, this is a temporary fix, and re-opening the worksheet switches back to the default scroll area. Therefore, let’s go through these steps.
- At the very beginning, go to the Developer tab >> then select Properties.
Now, this opens the Properties wizard.
- After that, click the Categorized tab >> next in the ScrollArea field, and enter the range of cells. In this case, the range is $A$1:$F$14.
📃 Note: Please make sure to provide an Absolute Cell Reference Shortcut by typing in the $ sign.
- Finally, close the Properties window and press CTRL+END to go to the end of the dataset. Moreover, your scrolling area is now limited to just your data range.
Solution-2: Deleting Contents of Other Cells to Stop CTRL+END from Going Too Far
Another common cause of the CTRL+END shortcut drifting away is that some data may be present in the other cells. Therefore, just follow these steps.
Suppose we have some text in the B1048563 cell.
- Initially, click on the column just right of the dataset to select it. For instance, we’ve selected Column F.
- Then, press the CTRL+SHIFT+Right Arrow key to select all the cells >> Next hit the CTRL+-(Minus) key to delete the contents of the cells.
- Following this, move to the row just below the dataset and select it. Here, it is Row 15.
- In turn, press the CTRL+SHIFT+Down Arrow key to select all the cells >> Now, press the CTRL+-(Minus) key to delete the cell contents.
- Lastly, type in CTRL+END to move to the end of the dataset.
Solution-3: Utilizing Immediate Window Feature to Prevent CTRL+END from Going Too Far
If the previous method is too much work then the next method simplifies the process using the Immediate Window in Visual Basic. It’s simple and easy, so just follow along.
- First of all, navigate to the Developer tab >> click the Visual Basic button.
This opens the Visual Basic Editor in a new window.
- Next, click the View tab >> from this list, and select the Immediate Window option.
This opens the Immediate Window at the bottom.
- Following this, copy and paste this command into the window and press the ENTER key on your keyboard.
ActiveSheet.UsedRange
- In the last step, close the Visual Basic Editor >> press the CTRL+END key and you should jump to the last cell of your dataset.
Solution-4: Hiding Rows and Columns to Stop CTRL+END from Going Too Far
If you don’t want to take the trouble of using VBA code you can hide the empty rows and columns in the spreadsheet. Hence, let us go through the instructions below.
- First and foremost, move to the column at the end of the dataset >> left-click on the mouse to select it. In this case, we’ve chosen Column G.
- Secondly, press the CTRL+SHIFT+Right Arrow key to select all the cells >> Right-click on the mouse and choose the Hide option.
Eventually, this hides all the cells to the right of the dataset.
- In a similar fashion, go to the row below the dataset and select it. For example, we’ve selected Row 16.
- Likewise, press the CTRL+SHIFT+Down Arrow key to select the cells with data below >> now, right-click on the mouse, and choose the Hide option.
Finally, your output should look like the screenshot given below.
Read More:Â How to Go to the End of Excel SheetÂ
Solution-5: Fixing Range of Cells with VBA Code
You can stop CTRL+END from going too far by limiting the scroll area with the VBA code. Now, allow me to demonstrate the process in the steps below.
- Firstly, navigate to the Developer tab >> click the Visual Basic button.
In an instant, the Visual Basic Editor appears in a new window.
- Secondly, go to the Insert tab >> select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub Reset_Last_Cell()
Dim x As Long
x = ActiveSheet.UsedRange.Rows.Count
End SubHere, are the Sheets.Select method activates the selected sheet (here it is Fixing Range of Cells with VBA). Following this, the ActiveSheet.ScrollArea property sets the scroll area to the specified range of cells (A1:F14).
- Now, close the VBA window >> in the Developer tab, and click on the Macros button.
This opens the Macros dialog box.
- Following this, select the Fix_Dataset_Range Macro >> press the Run button.
Consequently, press CTRL+END to go to the end of the dataset. In addition, the scrolling area is also limited.
Solution-6: Applying VBA Code to Stop CTRL+END from Going Too Far
The last method to stop CTRL+END from going too far also involves VBA code. It’s similar to the previous method, so, let’s see it in action.
- To begin with, follow 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 SubHere, define the variable x and assign the Long data type. Next, use the UsedRange property to count the number of rows and columns within the used range.
- Secondly, close the VBA window >> click the Macros button.
This opens the Macros dialog box.
- Finally, select the Reset_Last_Cell Macro >> press the Run button.
Subsequently, you can move to the last cell of the dataset as shown in the image below.
The methods above are all possible ways to Excel CTRL END goes too far. However, if the problem persists as the last option you can contact Microsoft Support. Here, you can find many Excel experts who will provide solutions for your particular issues.
Read More:Â How to Select Cells in Excel Using Keyboard
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
This article describes some quick and easy fixes when the Excel CTRL END goes too far. Hope you found it helpful. Please inform us in the comment section about your experience.
