I had been needing to find an easy way to determine the last used row in a given column, or possibly the next available row in a column. I search Google and found numerous examples, which I used for a short period of time. Code such as:

Range("A65536").End(xlup).Select

But that code was not 100% reliable and I wanted, as much as possible, to avoid useless .Select statement to try and optimize my code as best I could. Well, I finally managed to get it down to a single line of code without the use of any .Select statements and which is flexible regardless of the version of Excel being used. See my code below:

Dim lLastRow As Long
Dim lNextAvailableRow As Long

lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
lNextAvailableRow = lLastRow + 1

The only thing you need to know to use this code is that the 1 in (ActiveSheet.Rows.Count, 1) represents the columns to determine the last used row in. Hence 1=Column A, 2=Column B, …, 7=Column G, …

How does it work exactly? We use ActiveSheet.Rows.Count to get the total number of rows on the sheet and we use that as a starting point and then use End(xlUp) to move up until it has a value. Then from the matching cell we use .Row to return the row the last value was found.

If you don’t like having to use numeric values for the Column, you could use the following to enter a textual value and have Excel do the conversion to a numeric value:

ActiveSheet.columns("A").column

So we could then alter the above into:

Dim lLastRow As Long
Dim lNextAvailableRow As Long

lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, ActiveSheet.Columns("A").Column).End(xlUp).Row
lNextAvailableRow = lLastRow + 1

And now, you need only replace the “A” with whatever column you wish to find the last used row in.

So if we want to turn this into a proper function, we could simply do:

'---------------------------------------------------------------------------------------
' Procedure : GetLastUsedRowInColumn
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the last used row for the specified column
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sColumnLtr: The column letter to find the last row of
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2011-05-09              Initial Release
' 2         2024-11-28              Renamed function
'---------------------------------------------------------------------------------------
Public Function GetLastUsedRowInColumn(ByVal sColumnLtr As String) As Long
    On Error GoTo Error_Handler

    GetLastUsedRowInColumn = ActiveSheet.Cells(ActiveSheet.Rows.Count, _
                                               ActiveSheet.Columns(sColumnLtr).Column).End(xlUp).Row

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetLastUsedRowInColumn" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Now, you can simply use it in you code by doing, for instance to select the last row in Column “D”:

ActiveSheet.Range("D" & GetLastUsedRowInColumn("D")).Select

Or to select the next available row in Column “D”:

ActiveSheet.Range("D" & GetLastUsedRowInColumn("D") + 1).Select

So on and so forth.