Add new row to excel Table (VBA)

by

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

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  5. Levenshtein Distance in VBA [closed]
  6. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  7. How do I base64 encode a string efficiently using Excel VBA?
  8. Assign event handlers to controls on user form created dynamically in VBA
  9. Programmatic Access To Visual Basic Project Is Not Trusted
  10. Can I make this macro more efficient or faster?
  11. Conditional Formatting using Excel VBA code
  12. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  13. How to get the background color from a Conditional Formatting in Excel using VBA
  14. VBA Excel Populate ListBox with multiple columns
  15. VBA Integer Overflow at 70,000
  16. How to count up text of a different font colour in excel
  17. VBA Macro to compare all cells of two Excel files
  18. What does ReDim Preserve do?
  19. VBA – How the colon `:` works in VBA code with condition
  20. How to create a separate CSV file from VBA?
  21. VBA: How to change the value of another cell via a function?
  22. Running VBA from a HYPERLINK()
  23. Sending formatted Lotus Notes rich text email from Excel VBA
  24. How can I generate GUIDs in Excel?
  25. Remove Duplicates from range of cells in excel vba
  26. Excel Select vs Activate
  27. How to filter listbox values based on a Textbox value
  28. Find and FindNext for Excel VBA
  29. How do I get a list of unique values from a range in Excel VBA?
  30. How to get selected value in multicolumn listbox

Leave a Comment