Vba line of code to add data rows in my table

[Kingchaos64]

I have a vba code that used and made my own. I need to adjust where it's putting the new added rows. I have a table with headers at A5 to P5 and data rows start A6 and I have a total row at the end of the table. This is the code currently:

Private Sub cmdAdd_Click()

Dim iRow As Long

Dim ws As Worksheet

Set ws = Worksheets("Daily")





iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row



If Trim(Me.txtPart.Value) = "" Then

Me.txtPart.SetFocus

MsgBox "Please enter Date"

Exit Sub

End If





ws.Cells(iRow, 1).Value = Me.txtPart.Value

ws.Cells(iRow, 2).Value = Me.txtPart2.Value

ws.Cells(iRow, 3).Value = Me.txtPart3.Value

ws.Cells(iRow, 4).Value = Me.txtPart4.Value

ws.Cells(iRow, 5).Value = Me.txtPart5.Value

ws.Cells(iRow, 6).Value = Me.txtPart6.Value

ws.Cells(iRow, 7).Value = Me.txtPart7.Value

ws.Cells(iRow, 8).Value = Me.txtPart8.Value

ws.Cells(iRow, 9).Value = Me.txtPart9.Value

ws.Cells(iRow, 10).Value = Me.txtPart10.Value

ws.Cells(iRow, 11).Value = Me.txtPart11.Value

ws.Cells(iRow, 12).Value = Me.txtPart12.Value

ws.Cells(iRow, 13).Value = Me.txtPart13.Value

ws.Cells(iRow, 14).Value = Me.txtPart14.Value

ws.Cells(iRow, 15).Value = Me.txtPart15.Value

ws.Cells(iRow, 16).Value = Me.txtPart16.Value



Me.txtPart.Value = ""

Me.txtPart2.Value = ""

Me.txtPart3.Value = ""

Me.txtPart4.Value = ""

Me.txtPart5.Value = ""

Me.txtPart6.Value = ""

Me.txtPart7.Value = ""

Me.txtPart8.Value = ""

Me.txtPart9.Value = ""

Me.txtPart10.Value = ""

Me.txtPart11.Value = ""

Me.txtPart12.Value = ""

Me.txtPart13.Value = ""

Me.txtPart14.Value = ""

Me.txtPart15.Value = ""

Me.txtPart16.Value = ""

Me.txtPart.SetFocus


End Sub

What would I need to adjust in to make every row added stay in the table and automatically adjust the table each time a row is added?

I tried just changing
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
To
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
It puts the first row added in the right spot but the next row added just replaces the first and so on.

 

[maabadi]

Change iRow to this

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

AND After End if ADD this:

Rows(iRow).Insert

Also Try this:

Private Sub cmdAdd_Click()
Dim iRow As Long, ws As Worksheet, i As Long
Set ws = Worksheets("Daily")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter Date"
Exit Sub
End If

Rows(iRow).Insert
ws.Cells(iRow, 1).Value = Me.txtPart.Value
For i = 2 To 16
ws.Cells(iRow, i).Value = Me.Controls("txtPart" & i).Value
Next i

Me.txtPart.Value = ""
For i = 2 To 16
Me.Controls("txtPart" & i).Value = ""
Next i

Me.txtPart.SetFocus

End Sub

 

[Kingchaos64]

Change iRow to this

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

AND After End if ADD this:

Rows(iRow).Insert

Also Try this:

Private Sub cmdAdd_Click()
Dim iRow As Long, ws As Worksheet, i As Long
Set ws = Worksheets("Daily")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter Date"
Exit Sub
End If

Rows(iRow).Insert
ws.Cells(iRow, 1).Value = Me.txtPart.Value
For i = 2 To 16
ws.Cells(iRow, i).Value = Me.Controls("txtPart" & i).Value
Next i

Me.txtPart.Value = ""
For i = 2 To 16
Me.Controls("txtPart" & i).Value = ""
Next i

Me.txtPart.SetFocus

End Sub

Hahaha YES awesome thank you. It now works.. best day ever