Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Sub podziel()
Dim ile_liter As Byte

ile_liter = InStr(1, "Jan Kowalski", " ") - 1
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Left("Jan Kowalski", ile_liter)
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Right("Jan Kowalski", Len("Jan Kowalski") - ile_liter - 1)

'chociaż ostatni trend to

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Karol Nowak"
    
Cells(Rows.Count, 1).End(xlUp).TextToColumns Destination:=Cells(Rows.Count, 1).End(xlUp), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

End Sub