Function ffR() – Find First Free Row

When I want to add a new record to a table, I use the function CurrentRegion.Rows.Count and add 1 to the result to find the first empty row.
CurrentRegion returns the contiguous range from a spesific cell. The contiguous range is the range from A1 in the case below – with no empty rows.

Selecting CurrentRegion from the keyboard

Find First Free Row with CurrentRegion in Excel VBA

ffR(sh) returns the first free row from the specified sheet.

Usage exsample

Sub TestffR()

    Dim FirstFreeRow As Long
    FirstFreeRow = ffR(shData)
 
    Debug.Print FirstFreeRow

End Sub

Function code

'**********************************
'Return first free row in sheet sh
'**********************************
Function ffR(sh As Worksheet) As Long
    ffR = sh.Range("A1").CurrentRegion.Rows.Count + 1
End Function

Possible flaws – if you have an empty header in row 1, you might mess up your table…

Function ffR is a part of My Common Library: My Common Library (1438 downloads )