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!

Solution

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

Comments

[Ashton Sheets]

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.