_................Continued from last post

Here some steps along the simplification

_ Remove pasting out to worksheet (Output rows)

_ Not closing the large data Workbook

_ No Output unique Food product Column
( Remove pasting out to worksheet (Unique dictionary ( Food names ) ) )

_ Fixed Search Heading
( This is part of the start of hard coding to eliminating the code actually doing anything other than just referencing Range objects in a .Match ( , RangeObject , ) code line. The search heading is given a heading ( Margarinsäure ) at a row 3 and column a bit under 200, this does not test the actual heading range of 18 x almost 2000, but will allow a possible comparison later with a simple Range Object to Array conversion via_...
Array() = RangeObject.Value
_.... ( Arrays in Worksheet Functions being limited to 256 “columns” )
This last simplification causes as expected a reduction in the range references needed, but never mind that is made up in the next simplifications:

_ Removing any actual action left after the match condition is met ( This removes an Exit For so results in an increase in range references done. )

_ All but the main Loop is removed ( an inner Do While Inner Loop did things in the actual code which is not ( possibly !! ) so relevant to these academic digressions.

_ Remove all initial Array work: There is no longer any point of all the initial Array work involved with the data used in the sorting, so all is removed

_ Leave main data Workbook closed. There is no need to open at all the large data Workbook, the contribution to the main problem having been found not too relevant.

_ headings in same Workbook . Finally for one Code , the external Workbook with the actual heading range is done away with by copying the range to the same Workbook as the code and referencing that in the code.

Results / Conclusions:
Results:


USDAToDBArraysThirdWB – USDAToDBArraysThirdWBRaped
USDAToDBArraysThirdWB
00:21:33
Rows 340
rngRefs 153720


USDAToDBArraysThirdWBRaped
00:00:31
Rows 339
rngRefs 153413
No output row paste
00:00:24
Rows 338
rngRefs 153136
Not close data Workbook
00:00:24
Rows 339
rngRefs 153284
No Output Food Column
00:00:16
Rows 336
rngRefs 89045
Fixed Search Heading
00:00:46
Rows 225
rngRefs 366968
Remove all action after match
00:00:38
Rows 20445
rngRefs 368012
Remove all but outer loop
00:00:41
Rows 24661
rngRefs 443900
Remove all initial data Arrays
00:00:48
Rows 28207
rngRefs 507728
No open of data Workbook



USDAToDBArraysThirdWB18RngOffsets - USDAToDBArraysThirdWB18RngOffsetsRaped
USDAToDBArraysThirdWB18RngOffsets
00:00:23
Rows 336
rngRefs 152864


USDAToDBArraysThirdWB18RngOffsetsRaped
00:00:20
Rows 335
rngRefs 152255
No output row paste
00:00:18
Rows 337
rngRefs 152998
Not close data Workbook
00:00:19
Rows 338
rngRefs 153175
No Output Food Column
00:00:13
Rows 347
rngRefs 91043
Fixed Search Heading
00:00:35
Rows 225
rngRefs 367940
Remove all action after match
00:00:36
Rows 20361
rngRefs 366500
Remove all but outer loop
00:00:38
Rows 24602
rngRefs 442838
Remove all initial data Arrays
00:00:44
Rows 28307
rngRefs 509528
No open of data Workbook
00:00:44
Rows 28522
rngRefs 513408
Heading range in This workbook


Conclusions:

The last 4 - 5 lines in the tables show an expected slight improvement as other stuff is done away with, but not too significant compared with the order of magnitude improvement we are interested in.

Interesting that we require less references ( as expected ) when we fix the Search for heading to one early on, BUT do not get much further generally .. ?? – maybe the number of references in the Loop is less significant than the actual Loop number done ..

Anyway probably best break off now with a simplified code based on the last_....
Sub USDAToDBArraysThirdWB18RngOffsetsRaped()
_.... and use that in a new Thread to support this one....

_..........................

Continued in Thread: ....................
Thrashing VBA Memory Leaks. Memory Loop Problems. Uncleared Memory in .Match Range Object


http://www.excelforum.com/showthread...96#post4495796