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

Pressing CTRL+A in cell A1 will select the CurrentRegion
If content in row 6 is deleted, the CurrentRegion is limited to row 5 and column B

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

Similar Posts