Dim currentNr As String
Dim index As Integer

index = 2
currentNr = ActiveSheet.Cells(index, 1).Value

'schleife läuft solang bis eine leere Zeile kommt
While currentNr <> ""
    index = index + 1
    currentNr = ActiveSheet.Cells(index, 1).Value
End While

'ab hier steht in index der nächste freie zeilenindex nach der Tabelle

ActiveSheet.Cells(index, 2).Value = DTPicker1.Value
ActiveSheet.Cells(index, 3).Value = TextBox2.Value
ActiveSheet.Cells(index, 5).Value = TextBox3.Value
ActiveSheet.Cells(index, 6).Value = TextBox4.Value
ActiveSheet.Cells(index, 8).Value = ComboBox1.Value
ActiveSheet.Cells(index, 9).Value = TextBox5.Value