Forum Discussion
Solved
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...
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 SubNikolinoDE Nope, the code it's work, but don't happen nothing when i filter and after i clean the filter
