What is the proper way to insert values into an excel table using VBA?

Question

I need to be able to insert a large set of values into an empty table in excel using VBA code. Here's how the code works so far.

First, the user inputs a value into a userform. The code then clears the table then goes and finds a range of numbers based on lookup criteria already in the code. The retrieved data is all contained within a single column and stored like an array.

From here, I need to place all of the values into a certain column in the table (Policy #), thus expanding the table rows to be however many rows were in the set of retrieved data. (I do have the count already stored separately as "AC" if it's needed) The column's header I want to insert into is "Policy #".

Keeping in mind that there is only a blank row in the table at the present time of the code, how can I insert the data properly? I've tried

 range("commissionstatement[Policy #]").value = Als

but that doesnt work. Als is the array of values by the way. Usually to insert the array I have to insert into a range equal in size which is why I took the row count as AC already.

I've also tried using range("commissionstatement").listobject.listrows but that doesnt work either.

Any advice? Am I going to need to insert a number of rows into the table equal to the number of data im adding, before I actually place the data in there like this...

range("commissionstatement").listobject.listrows.add ()

followed by inserting the data?

Let me know if more info is needed. Thanks!

score 6 · Accepted Answer · answered Aug 23 '12 at 05:54

Assuming you are using Excel 2010, try this (may not work with earlier versions of Excel)

Sub AddToList()
    Dim lo As ListObject
    Dim ws As Worksheet
    Dim Als() As Variant
    Dim rng As Range

    Set ws = ActiveSheet

    ' Get reference to table
    Set lo = ws.ListObjects("MyTable")  ' <--- Update this with your table name

    If lo.InsertRowRange Is Nothing Then
        ' List already has data
        Set rng = lo.ListRows.Add.Range
    Else
        ' List is empty
        Set rng = lo.InsertRowRange
    End If

    '  *** Remove one of these two lines ***
    ' If Als is a 1 dimensional array
    rng.Cells(1, lo.ListColumns("Policy #").Index).Resize(UBound(Als) - LBound(Als) + 1, 1) = Application.Transpose(Als)

    ' If Als is 2 dimensional array (1 to rows, 1 to 1)
    rng.Cells(1, lo.ListColumns("Policy #").Index).Resize(UBound(Als, 1), 1) = Als
End Sub

I like how that looks, I'll definitely give it a shot. I made a workaround that did the trick temporarily but it's not nearly as dynamic and flexible as when the table is properly resized like in your example. — Ashton Sheets, Aug 23 '12 at 13:54

score 0 · Answer 2 · answered Oct 02 '14 at 11:53

This worked for me. I'm also filling an empty ListObject with data from an array. For this to work the array matriu must be a 2-dimensional array.

Dim matriu() As Variant
Dim ls As ListObject

Set ls = Hoja1.ListObjects(1)

' Retrieve data
matriu = Hoja1.Range("Origen")

' Erease all rows of the ListObject, if needed
ls.DataBodyRange.Delete

' This line is needed in order for the next line 
' not to throw an error if the listobject is empty.
ls.ListRows.Add

' Fill the ListObject with all data in one operation to reduce execution time    
Range(ls.DataBodyRange.Cells(1, 1), _
   ls.DataBodyRange.Cells(UBound(matriu, 1), UBound(matriu, 2))) = matriu

What is the proper way to insert values into an excel table using VBA?

2 Answers2