I have four command buttons. Each one adds a row to a different table. I would like to be able to add more than one row based on the value of cell "L5". If I click button 1 and the value of "L5" is 10, I want it to add 10 rows to that table, same if I use button 2.
Here is the current code I am using for command button 1:
Sub PV_Field_Add_Row()
'
' PV_Field_Add_Row Macro
'
'
Range("Table15").Select
Selection.ListObject.ListRows.Add (1)
End Sub
Using For
to add more rows on Excel table (ListObject)
Microsoft documentation:
Sub PV_Field_Add_Row()
Dim iVal, Sht As Worksheet
Set Sht = Worksheets("Sheet1") ' modify as need
iVal = Sht.Range("L5").Value
If IsNumeric(iVal) Then
Dim i As Long
For i = 1 To iVal
Sht.ListObjects("Table15").ListRows.Add
Next
End If
End Sub
Thanks for @Tim
's comment. It's a better solution.
Note: If there isn’t sufficient space (blank rows) below the ListObject, a runtime error will occur. For example, if the ListObject is directly above the PivotTable with only one blank row in between. Insert
rows is necessary before resize ListObject.
Microsoft documentation:
Sub PV_Field_Add_Row2()
Dim iVal, Sht As Worksheet
Set Sht = Worksheets("Sheet1") ' modify as need
iVal = Sht.Range("L5").Value
If IsNumeric(iVal) Then
With Sht.ListObjects("Table1")
Dim iCnt As Long
iCnt = .Range.Rows.Count
.Range.Rows(iCnt).Offset(1).Resize(iVal).EntireRow.Insert
.Resize .Range.Resize(iCnt + iVal)
End With
End If
End Sub