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