VBA skips cells when data is entered

Question

I have an issue with my VBA code, maybe someone can explain where I made a mistake and how can it be fixed. My goal is to create 7 columns without skipping cells. I have 7 text boxes but not all of them has to be filled every time data is entered. At the moment when data is added only in Name text box it creates new row but when only surname is added it skips first empty lines and enters diagonally under Name entry(as in attachment) Excel img

Also there is a mistake when I want to add entry in other text boxes, entered data is just moved around.

Private Sub CommandButton1_Click()

   Dim LR As Long

   LR = Cells(Rows.Count, 1).End(xlUp).Row + 1

   Cells(LR, 1).Value = Name.Value
   Cells(LR, 2).Value = Surname.Value
   Cells(LR, 3).Value = Address.Value
   Cells(LR, 4).Value = Phone.Value
   Cells(LR, 5).Value = City.Value
   Cells(LR, 6).Value = Car.Value
   Cells(LR, 7).Value = Job.Value

   Name.Value = ""
   Surname.Value = ""
   Address.Value = ""
   Phone.Value = ""
   City.Value = ""
   Car.Value = ""
   Job.Value = ""


End Sub

Answer 1

You are always looking for the last row in column A, thats why it adds the surname one row below the last row of column A. If you want to know the last row of another column, you need to change the 1 after Rows.Count into the corresponding number. You can also do this in one line, have a gander:

Private Sub CommandButton1_Click()

   Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = Name.Value
   Cells(Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).Value = Surname.Value
   Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Value = Address.Value
   Cells(Cells(Rows.Count, 4).End(xlUp).Row + 1, 4).Value = Phone.Value
   Cells(Cells(Rows.Count, 5).End(xlUp).Row + 1, 5).Value = City.Value
   Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 6).Value = Car.Value
   Cells(Cells(Rows.Count, 7).End(xlUp).Row + 1, 7).Value = Job.Value

   Name.Value = ""
   Surname.Value = ""
   Address.Value = ""
   Phone.Value = ""
   City.Value = ""
   Car.Value = ""
   Job.Value = ""

End Sub