CODEWITHSUNDEEP
vbaexcel
adam
adam

Reputation: 424

how to insert array list into table?

I was wondering if there is a faster way to insert a list of values into a table object. The method i'm using goes like this:

1) Clear the old data from table

2) find add in new date values to the table and letting the table formulas auto-populate.

The problem right now is that this process is incredibly slow as i have about 7 tables and 4k+ dates to populate.

Ordinarily if it's just pasting the values, excel works incredibly fast. But when it comes to table objects, it gets a lot slower for some reason.

I think there's a way to do this a lot faster by manipulating the table object but i haven't had any luck so far. 

*table1
     1          2       3       4       5       6
 A   Date       1D      2D      3D      4D      5D
 B   1/1/2016   Formula Formula Formula Formula Formula
 C   2/1/2016   Formula Formula Formula Formula Formula
 D   3/1/2016   Formula Formula Formula Formula Formula
 E   4/1/2016   Formula Formula Formula Formula Formula
 F   5/1/2016   Formula Formula Formula Formula Formula
 G   6/1/2016   Formula Formula Formula Formula Formula
 H   7/1/2016   Formula Formula Formula Formula Formula

Dim ws(), datelist() As Variant
For i = 1 To UBound(ws)
    For j = 1 To UBound(datelist)
        Sheets(CStr(ws(i))).Cells(j + 1, 1) = datelist(j)
    Next j
Next i

*ws() refers to array where i store worksheet names

*datelist() refers to the dates i want to paste into the cells.

not sure if my description of the problem is clear enough.

Upvotes: 0

Views: 1448

Answers (1)

Gary Evans
Gary Evans

Reputation: 1890

Doing a quick search brought up this answer.

To apply it to your case, instead of: -

For i = 1 To UBound(ws)
    For j = 1 To UBound(datelist)
        Sheets(CStr(ws(i))).Cells(j + 1, 1) = datelist(j)
    Next j
Next i

Use: -

For i = 1 To UBound(ws)
    Sheets(CStr(ws(i))).Range("A" & j + 1 & ":A" & j + (1 + UBound(datelist))) = WorksheetFunction.Transpose(datelist)
Next i

Upvotes: 1

Related Questions