Using VBA to create a big table from two tables that reference each other - Too Slow

[dbrennan]

Hello,

I have written a macro that creates a new Table based on the values (rows and columns) of Table1 but adds in the Values of a referenced Table2. Replicating a many to one relationship to create a combined solution. The macro works fine but as the tables are getting larger (Table 1 has 400 rows and Table 2 has 150 rows and Columns to CQ), the macro now takes several minutes to complete.

The macro simple loops through each row of table 1 and then uses the referenced column to replicate all the related rows in the lookup table. I'm wondering if there a different method that isn't so intensive and time consuming?

Please Login or Register  to view this content.

[Greg M]

Hi there,

We'd probably be much more inclined to investigate a solution if we had a typical workbook to "play" with - can you post something to help us out here?

I suspect that a large part of the execution time is taken up by copying values one cell at a time. A much more efficient way to copy and paste is to assign the values in the source range to an array, and to then use the values in that array to populate the cells in the target range.

These "assign" and "populate" operations are performed in bulk rather than on a cell-by-cell basis.

I might be able to suggest something a bit more specific if I could see what your workbook looks like.

Regards,

Greg M

[dbrennan]

Copy of System Design Tool - Rev 1 - Copy.xlsm

Ive attached an example file for testing.

[jindon]

try

Please Login or Register  to view this content.