将新行添加到 Excel 表 (VBA)

Add new row to excel Table (VBA)

提问by Miguel Teixeira

I have an excel which serves to record the food you ingest for a specific day and meal. I hav a grid in which each line represent a food you ate, how much sugar it has, etc.

我有一个 excel，用于记录您在特定日期和膳食中摄入的食物。我有一个网格，其中每条线代表你吃的食物，它含有多少糖，等等。

Then i've added an save button to save all the data to a table in another sheet.

然后我添加了一个保存按钮以将所有数据保存到另一个工作表中的表格中。

This is what i have 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 i am doing wrong?

我在做什么错？

Thanks in advance!

提前致谢！

回答by chris neilsen

You don't say which version of Excel you are using. This is written for 2007/2010 (a different apprach is required for Excel 2003 )

您没有说明您使用的是哪个版本的 Excel。这是为 2007/2010 编写的（Excel 2003 需要不同的方法）

You also don't say how you are calling addDataToTableand what you are passing into arrData.
I'm guessing you are passing a 0based array. If this is the case (and the Table starts in Column A) then iCountwill count from 0and .Cells(lLastRow + 1, iCount)will try to reference column 0which is invalid.

你也没有说你是如何打电话的addDataToTable以及你正在传递什么arrData。
我猜你正在传递一个0基于数组。如果是这种情况（并且表格从 Column 开始A），那么iCount将从开始计数0并.Cells(lLastRow + 1, iCount)尝试引用0无效的列。

You are also not taking advantage of the ListObject. Your code assumes the ListObject1 is located starting at row 1. If this is not the case your code will place the data in the wrong row.

您也没有利用ListObject. 您的代码假定ListObject1 位于从 row 开始的位置1。如果不是这种情况，您的代码会将数据放在错误的行中。

Here's an alternative that utilised the ListObject

这是一个利用 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

Can be called in a variety of ways:

可以通过多种方式调用：

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

回答by user1058322

Tbl.ListRows.Adddoesn't work for me and I believe lot others are facing the same problem. I use the following workaround:

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

Hope it helps someone out there.

希望它可以帮助那里的人。

回答by kskoeld

I had the same error message and after lots of trial and error found out that it was caused by an advanced filter which was set on the ListObject. After clearing the advanced filter .listrows.add worked fine again. To clear the filter I use this - no idea how one could clear the filter only for the specific listobject instead of the complete worksheet.

我有相同的错误消息，经过大量试验和错误后发现它是由 ListObject 上设置的高级过滤器引起的。清除高级过滤器后 .listrows.add 再次正常工作。为了清除过滤器，我使用了这个 - 不知道如何只为特定的列表对象而不是完整的工作表清除过滤器。

Worksheets("mysheet").ShowAllData

回答by fireball8931

I actually just found that if you want to add multiple rows below the selection in your table Selection.ListObject.ListRows.Add AlwaysInsert:=Trueworks really well. I just duplicated the code five times to add five rows to my table

我实际上刚刚发现，如果您想在表格中的选择下方添加多行， Selection.ListObject.ListRows.Add AlwaysInsert:=True效果非常好。我只是将代码复制了五次以在我的表中添加五行

回答by Djamel Ben

I had the same problem before and i fixed it by creating the same table in a new sheet and deleting all the name ranges associated to the table, i believe whene you're using listobjects you're not alowed to have name ranges contained within your table hope that helps thanks

我之前遇到过同样的问题，我通过在新工作表中创建同一个表并删除与该表相关联的所有名称范围来修复它，我相信当您使用列表对象时，您不允许将名称范围包含在您的表希望有帮助谢谢

回答by ErikB

Ran into this issue today (Excel crashes on adding rows using .ListRows.Add). After reading this post and checking my table, I realized the calculations of the formula's in some of the cells in the row depend on a value in other cells. In my case of cells in a higher column AND even cells with a formula!

今天遇到了这个问题（Excel 在使用添加行时崩溃.ListRows.Add）。阅读这篇文章并检查我的表格后，我意识到该行中某些单元格中公式的计算取决于其他单元格中的值。在我的情况下，更高列中的单元格甚至带有公式的单元格！

The solution was to fill the new added row from back to front, so calculations would not go wrong.

解决方案是从后向前填充新添加的行，这样计算就不会出错。

Excel normally can deal with formula's in different cells, but it seems adding a row in a table kicks of a recalculation in order of the columns (A,B,C,etc..).

Excel 通常可以处理不同单元格中的公式，但似乎在重新计算的表格中按列（A、B、C 等）的顺序添加一行。

Hope this helps clearing issues with .ListRows.Add

希望这有助于解决问题 .ListRows.Add

回答by Bhanu Sinha

Just delete the table and create a new table with a different name. Also Don't delete entire row for that table. It seems when entire row containing table row is delete it damages the DataBodyRange is damaged

只需删除该表并创建一个具有不同名称的新表。也不要删除该表的整行。似乎当包含表行的整行被删除时，它会损坏 DataBodyRange 已损坏

回答by Jonas_Hess

As using ListRow.Addcan be a huge bottle neck, we should only use it if it can't be avoided. If performance is important to you, use this function here to resize the table, which is quite faster than adding rows the recommended way.

由于使用ListRow.Add可能是一个巨大的瓶颈，我们应该只在无法避免的情况下使用它。如果性能对您很重要，请在此处使用此函数来调整表的大小，这比以推荐的方式添加行要快得多。

Be aware that this will overwrite databelow your table if there is any!

请注意，这将覆盖表格下方的数据（如果有）！

This function is based on the accepted answer of Chris Neilsen

此功能基于Chris Neilsen已接受的答案

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