Forum Discussion
how to return to the last line write in excel after cleaning a filter
Hello everyone,
I need a little help.
I manage the hardware management of the IT department, and I have an excel with 4200 lines, on the column heads there are filters and I would like to get rid of the irritation that when I filter on a certain column and clean the filter to bring me the excel from row 1, could I somehow that every time after I clean the filter to bring me back on the last row written, like 4201 and so on?
It's frustrating because I have to scrounge from row 1 to row 4200 something...
Thank you in advance
CTRL+END always goes to the lower-right corner of the used range.
If this not where your data ends then delete the extra row/columns and save the workbook.
Alternatively, you can use VBA to achieve the desired behavior of returning to the last row written after cleaning a filter in Excel.
Here is an example code.
Private Sub Worksheet_Deactivate() ' Store the address of the active cell before deactivating the worksheet ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = ActiveCell.Address End Sub Private Sub Worksheet_Activate() ' Activate the last cell address stored before deactivating the worksheet Dim lastCell As String lastCell = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value If lastCell <> "" Then Range(lastCell).Activate End If End Sub- Doesn't work, Run-time error '9': Subscript out of range.
Please try the following adjusted VBA code:
Private Sub Worksheet_Deactivate() On Error Resume Next ' Store the address of the active cell before deactivating the worksheet ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = ActiveCell.Address On Error GoTo 0 End Sub Private Sub Worksheet_Activate() On Error Resume Next ' Activate the last cell address stored before deactivating the worksheet Dim lastCell As String lastCell = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value If lastCell <> "" Then ActiveWorkbook.Sheets("Sheet1").Range(lastCell).Activate End If On Error GoTo 0 End Sub
There is CTRL+END.
- Doesn't work, instead of cell A4420 it takes me to cell K4774 😞
CTRL+END always goes to the lower-right corner of the used range.
If this not where your data ends then delete the extra row/columns and save the workbook.
