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
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…