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

https://en.excelguru.no/wp-content/uploads/2020/07/CurrentRegion.mp4

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 (1050 downloads )