Add new row to excel Table VBA

Question

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    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

User · Answer

As using ListRow Add can 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   Be aware that this will overwrite data below your table if there is any   This function is based on the accepted answer of  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

User · Answer

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

User · Answer

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

User · Answer

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   Worksheets  mysheet   ShowAllData

User · Answer

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   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      Hope this helps clearing issues with  ListRows Add

User · Answer

Tbl ListRows Add doesn t work for me and I believe lot others are facing the same problem  I use the following workaround        First check if the last row is empty  if not  add a row     If table ListRows count  gt  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    lt  gt     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

User · Answer

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

User · Answer

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    You also don t say how you are calling addDataToTable and what you are passing into arrData  I m guessing you are passing a 0 based array   If this is the case  and the Table starts in Column A  then iCount will count from 0 and  Cells lLastRow   1  iCount  will try to reference column 0 which is invalid   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   Here s an alternative that utilised the 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

[vba] Add new row to excel Table (VBA)

Examples related to vba

Examples related to excel