
Viorel 113.8K Reputation points
20210225T20:54:49.157+00:00 A series of articles mentions that getting all values as an array increases the performance.
To read the arrays of source and destination worksheets, try a code like this:
Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("Source") Set ws2 = Worksheets("Dest") Dim r1 As Range, r2 As Range Set r1 = ws1.UsedRange Set r2 = ws2.UsedRange Dim d1, d2 d1 = r1.Value d2 = r2.Value
Here d1 and d2 represent bidimensional arrays (matrices) with source and destination values. Indices like d1(1,1), d1(1,2), d1(1,3), etc. is the first row, d1(2,1), d1(2,2), d1(2,3) etc. is the second row and so on.
Note that d1 and d2 include the used range only, therefore d1(1,1) and d2(1,1) do not necessarily represent the A1 cell if the top or left area is empty.
Also note that the sizes of d1 and d2 can be different.
Using d1, d2 (and maybe ws1.UsedRange and ws2.UsedRange) variables you can make the corresponding loops. To change some destination value, use something like d2(y,19) = d1(x,8). Make sure that your code does not exceed the array bounds.
Finally execute ws2.UsedRange.Value = d2 to update the destination worksheet.
This seems to require some effort to implement, but it should work much faster.
Re: comparing 1 column against another
With each set of data having 3000 cells, you are making 9 million comparisons (3000 * 3000).
That is why it takes so long.
It would much quicker to 'Match' each cell in Dest against the entire column in Source...
That would required only 3000 matches. (if there are no duplicates in Source)
Something like...
Application.WorksheetFunction.Match(.Cells(y, 1), Source.Column(1), 1, 0)
'
NLtL
https://1drv.ms/u/s!Au8Lyt79SOuhZ_2VvKCLZxz9iwI?e=vnEabM
Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare