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

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

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

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

Similar Posts