![]()
_................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 153413No output row paste 00:00:24 Rows 338 rngRefs 153136Not close data Workbook 00:00:24 Rows 339 rngRefs 153284No Output Food Column 00:00:16 Rows 336 rngRefs 89045Fixed Search Heading 00:00:46 Rows 225 rngRefs 366968Remove all action after match 00:00:38 Rows 20445 rngRefs 368012Remove all but outer loop 00:00:41 Rows 24661 rngRefs 443900Remove all initial data Arrays 00:00:48 Rows 28207 rngRefs 507728No open of data Workbook
USDAToDBArraysThirdWB18RngOffsets - USDAToDBArraysThirdWB18RngOffsetsRaped
USDAToDBArraysThirdWB18RngOffsets
00:00:23 Rows 336 rngRefs 152864
USDAToDBArraysThirdWB18RngOffsetsRaped
00:00:20 Rows 335 rngRefs 152255No output row paste 00:00:18 Rows 337 rngRefs 152998Not close data Workbook 00:00:19 Rows 338 rngRefs 153175No Output Food Column 00:00:13 Rows 347 rngRefs 91043Fixed Search Heading 00:00:35 Rows 225 rngRefs 367940Remove all action after match 00:00:36 Rows 20361 rngRefs 366500Remove all but outer loop 00:00:38 Rows 24602 rngRefs 442838Remove all initial data Arrays 00:00:44 Rows 28307 rngRefs 509528No open of data Workbook 00:00:44 Rows 28522 rngRefs 513408Heading 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
Bookmarks