how to return to the last line write in excel after cleaning a filter

[Andrey3339]

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 

[#2]

@Andrey3339 

There is CTRL+END.

[#3]

@Andrey3339 

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

[#4]

Doesn't work, instead of cell A4420 it takes me to cell K4774 :(

[#5]

Doesn't work, Run-time error '9': Subscript out of range.

[#6]

@Andrey3339 

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

[#7]

@NikolinoDE Nope, the code it's work, but don't happen nothing when i filter and after i clean the filter 

[#8]

do I have to replace the constant Sheet1 as I named the sheet myself? and A1 stays like that?

[#9 · Accepted solution]

@Andrey3339 

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.