Vba line of code to add data rows in my table

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,590
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change iRow to this
VBA Code:
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
AND After End if ADD this:
VBA Code:
Rows(iRow).Insert

Also Try this:
VBA Code:
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
 
Last edited:
Solution

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Change iRow to this
VBA Code:
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
AND After End if ADD this:
VBA Code:
Rows(iRow).Insert

Also Try this:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,215
Messages
5,623,431
Members
415,974
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top