Geek asks and answers

Add a new row to an Excel spreadsheet (VBA)

I have the superiority that serves to record the food you eat during a certain day and food. I have a grid in which each row represents the food you ate, how much sugar it has, etc.

Then I added a save button to save all the data in the table on another sheet.

This is what I tried

    Public Sub addDataToTable(ByVal strTableName As String, ByRef arrData As Variant)
    Dim lLastRow As Long
    Dim iHeader As Integer
    Dim iCount As Integer

    With Worksheets(4).ListObjects(strTableName)
        'find the last row of the list
        lLastRow = Worksheets(4).ListObjects(strTableName).ListRows.Count

        'shift from an extra row if list has header
        If .Sort.Header = xlYes Then
            iHeader = 1
        Else
            iHeader = 0
        End If
    End With

    'Cycle the array to add each value
    For iCount = LBound(arrData) To UBound(arrData)
        **Worksheets(4).Cells(lLastRow + 1, iCount).Value = arrData(iCount)**
    Next iCount
End Sub

but I keep getting the same error on the highlighted line:

Application-defined or object-defined error

What am I doing wrong?

Thanks in advance!

+5
source share
7 answers

You do not say which version of Excel you are using. This is written for 2007/2010 (Excel 2003 requires a different rating)

, addDataToTable arrData.
, 0. ( A), iCount 0, .Cells(lLastRow + 1, iCount) 0, .

ListObject. , ListObject 1 1. , .

, ListObject

Sub MyAdd(ByVal strTableName As String, ByRef arrData As Variant)
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    ' Based on OP 
    ' Set Tbl = Worksheets(4).ListObjects(strTableName)
    ' Or better, get list on any sheet in workbook
    Set Tbl = Range(strTableName).ListObject
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

    ' Handle Arrays and Ranges
    If TypeName(arrData) = "Range" Then
        NewRow.Range = arrData.Value
    Else
        NewRow.Range = arrData
    End If
End Sub

:

Sub zx()
    ' Pass a variant array copied from a range
    MyAdd "MyTable", [G1:J1].Value
    ' Pass a range
    MyAdd "MyTable", [G1:J1]
    ' Pass an array
    MyAdd "MyTable", Array(1, 2, 3, 4)
End Sub
+13

Tbl.ListRows.Add , , . :

    'First check if the last row is empty; if not, add a row
    If table.ListRows.count > 0 Then
        Set lastRow = table.ListRows(table.ListRows.count).Range
        For col = 1 To lastRow.Columns.count
            If Trim(CStr(lastRow.Cells(1, col).Value)) <> "" Then
                lastRow.Cells(1, col).EntireRow.Insert
                'Cut last row and paste to second last
                lastRow.Cut Destination:=table.ListRows(table.ListRows.count - 1).Range
                Exit For
            End If
        Next col
    End If

    'Populate last row with the form data
    Set lastRow = table.ListRows(table.ListRows.count).Range
    Range("E7:E10").Copy
    lastRow.PasteSpecial Transpose:=True
    Range("E7").Select
    Application.CutCopyMode = False

, - .

+3

, , , ListObject. .listrows.add . , - , , .

Worksheets("mysheet").ShowAllData
+2

, Selection.ListObject.ListRows.Add AlwaysInsert:=True . ,

+1

, , , , , , , , ,

+1

. . , , , , DataBodyRange.

-1

Since use ListRow.Addcan be a huge bottleneck, we should only use it if it can be avoided. If performance is important to you, use this function here to resize the table, which is much faster than adding rows in the recommended way.

Keep in mind that this will overwrite the data under your table, if any!

This feature is based on Chris Nielsen's accepted answer.

Public Sub AddRowToTable(ByRef tableName As String, ByRef data As Variant)
    Dim tableLO As ListObject
    Dim tableRange As Range
    Dim newRow As Range

    Set tableLO = Range(tableName).ListObject
    tableLO.AutoFilter.ShowAllData

    If (tableLO.ListRows.Count = 0) Then
        Set newRow = tableLO.ListRows.Add(AlwaysInsert:=True).Range
    Else
        Set tableRange = tableLO.Range
        tableLO.Resize tableRange.Resize(tableRange.Rows.Count + 1, tableRange.Columns.Count)
        Set newRow = tableLO.ListRows(tableLO.ListRows.Count).Range
    End If

    If TypeName(data) = "Range" Then
        newRow = data.Value
    Else
        newRow = data
    End If
End Sub
-1
source

More articles:


All Articles