Return to the Last Used Row After Clearing a Filter in Excel
Question details
The user wants to return to the last used area or the previously selected cell after clearing a worksheet filter.
- Product
- Microsoft Excel worksheet filters and VBA macros
- Device & OS
- not provided
- Scenario
- A filtered worksheet returns to the top after the filter is cleared, and the user needs to navigate back to the last relevant row or prior selection.
- Observed behavior
- After clearing the filter, the worksheet view may move to the top instead of remaining near the last used row or selected cell.
Confirm that the worksheet has a correct used range before relying on Ctrl+End. If you plan to use VBA, save the workbook as a macro-enabled file and enable macros when opening it.
Use Ctrl+End to go to the worksheet’s last used cell
Use this quick navigation method when you only need to return to the lower-right cell of the current used range after clearing a filter.
On the Data tab, select Clear in the Sort & Filter group, or use the filter drop-down and choose Clear Filter From the column.
Press Ctrl+End. Excel moves the active cell to the last cell in the worksheet’s used range, typically near the last used row.
If Ctrl+End moves far beyond your actual data, select the unused rows below and columns to the right of the data, right-click and choose Delete, then save the workbook and reopen it.
Use worksheet VBA events to restore the last selected cell
Use this approach when you want the worksheet to automatically return to the cell that was selected before you left the sheet.
Navigate to the Last Used Cell and Automate Cell Restoration in WPS Spreadsheet
WPS Spreadsheet lets you use Ctrl+End to jump to the end of the used range after clearing filters. For repeated workflows, you can also use supported macro tools to save and restore a selected cell when returning to a worksheet.
- 1. Open the filtered worksheet: Open your workbook in WPS Spreadsheet and select the worksheet containing the filtered data.
- 2. Clear the filter: Select Data > Clear to remove active filters from the worksheet.
- 3. Jump to the used range endpoint: Press Ctrl+End to move to the lower-right cell of the worksheet’s used range.
- 4. Review extra blank ranges: If the selection reaches unnecessary blank rows or columns, delete the unused range and save the workbook to refresh the used area.
Frequently Asked Questions
Why does Excel return to the top of the worksheet after I clear a filter?
Clearing a filter can reset the visible position of the worksheet. Press Ctrl+End to move directly to the final cell in the current used range.
What does Ctrl+End do in an Excel worksheet?
Ctrl+End selects the lower-right cell of Excel’s used range. This may be the last data cell or a farther cell if unused cells still contain formatting or old content.
How can I make Ctrl+End stop at my actual last row?
Delete unused rows below the data and unused columns to the right, then save the workbook. Reopening the file can help Excel recalculate the used range.
Where should I paste the Worksheet_Activate VBA code?
Press Alt+F11, find the target worksheet under Microsoft Excel Objects in Project Explorer, and paste the Worksheet_Activate and Worksheet_Deactivate procedures into that worksheet’s code module.
Will the VBA solution work if macros are disabled?
No. The workbook must be saved as an .xlsm file, and macros must be enabled when the workbook is opened for the worksheet event procedures to run.
