excelvba

ActiveX command button that adds a number of rows based on the value of cell "L5"


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

Solution

  • Using For to add more rows on Excel table (ListObject)

    Microsoft documentation:

    ListRows.Add method (Excel)

    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:

    ListObject.Resize method (Excel)

    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