Big List Sort. Not Enough Memory. Help Run my Code. Or help fix or change code Please :)

VBA is a weird Bugger when it Sets Ranges. It like Offsets. It holds ‘em differently

Just a quick update / recap before I move on or lose what is left of my sanity...

Codes looping a Line pseudo like
= .Match( , SecondArgumentRangeObject , )
or
= .Match( , rnglongSrcRow_x , )
Where x can vary for a different “Long Row” Range Object.

Codes where SecondArgumentRangeObject changed in each Loop by different Offset to a specific Range
Sub USDAToDBArraysThirdWB18RngOffsets()
Set rnglongSrcRow1 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(1, 11), wsDBlx.UsedRange.Cells(1, lcDBlx))
Set rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed, 0)
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 348 rows at 24 Seconds

Sub USDAToDBArraysThirdWB18RngOffsets()
Set rnglongSrcRow1 = wsDBlx.Range(wsDBlx.Cells(1, 11), wsDBlx.Cells(1, lcDBlx))
rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed, 0)
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 343 rows at 25 Seconds
_.....................

Codes where .Match code line is repeated so that each one uses a fixed Range Object
Sub USDAToDBArraysThirdWB18RngObjects()
Set rnglongSrcRow4 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(4, 11), wsDBlx.UsedRange.Cells(4, lcDBlx))
x 18 etc ( just “row” 4 given here )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 336 rows at 23 Seconds


Sub USDAToDBArraysThirdWB18RngObjects()
Set rnglongSrcRow4 = wsDBlx.Range(wsDBlx.Cells(4, 11), wsDBlx.Cells(4, lcDBlx))
x 18 etc ( just “row” 4 given here )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 23 Seconds

Some slight variations on the couple above
Sub USDAToDBArraysThirdWB18RngObjects2()
Set rnglongSrcRow4 = wsDBlx.Range(wsDBlx.Cells(4, 11), wsDBlx.Cells(4, lcDBlx))
With the entire 18 Set Long Row Ranges within the Loop
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 341 rows at 25 Seconds

Sub USDAToDBArraysThirdWB18RngObjectsFrmOfst()
( The range Objects are made ( Set ) from Offset from first Long Range Row Object )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 348 rows at 24 Seconds

Back to the original Codes for a check:
Sub USDAToDBArraysThirdWB()
Here a code line like
Set rnglongSrcRow = wsDBlx.Range(wsDBlx.Cells(rDBlxHed, 11), wsDBlx.Cells(rDBlxHed, lcDBlx))
Sets the range each time. The same Long type variable, rDBlxHed, used in previous codes is used to specifically reference the Range of interest each time in the Loop
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 21.... .. Minutes !!

Sub USDAToDBSpreadsheetThirdBook()
This is a simple "spreadsheet interaction code, - as a matched heading is found, the Nutrition value is pasted directly to the Output Workbook ( DBSept2016.xlsx ) - so no building of Array of multiple values to Paste out in one go
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 368 rows at 25...... seconds!.. This is a bit wiered.. the code is basically the same as the one that takes 21 minutes ??
Because of this last bit, I added a line to paste out arbritrarily to Workbook DBSept2016.xlsx in the code Sub USDAToDBArraysThirdWB(). So this effectively theoretically does away with the advantages of doing it in Arrays then pasting out in one go..
_... the results were then
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 342 rows at 25 Seconds. Another mad result!!

_.. Just putting all that in words

If I have a code line, pseudo

= .Match( , SecondArgumentRangeObject , )
and I have it in a Loop.

It takes in my codes
_1 ) Minutes if
SecondArgumentRangeObject
is Set at each loop to a different Range Object ( specifically like Set = Range(“___”) )

_2 ) seconds if

_either
SecondArgumentRangeObject
Is always the same Range Object
_ or ( and this is the amazing thing )
SecondArgumentRangeObject
is Set at each loop to a different Range Object via some offset to a fixed Range.

One could say, VBA does not like to change the Range Object that is used in a = .Match( , SecondArgumentRangeObject , ), unless that changed Object is changed via an Offset to a single Fixed Range Object ( But if you interact with the Worksheet it settles down !!! )

In other words if I have _...

rngX = Range(“A2”)

and

rngY = Range(“A1”).Offset(1, 0)

_.... they both refer to Cell A2

But Excel VBA handles them differently

As long as all my different ranges are Set as an Offset ( that Offset can vary each time ) to any Range, then my code will run about
60 times quicker than if I Set each range to the specific Range I want.

That is weird!!! ( and The last bit, is either weird , or an amazing discovery !! )*** And to take the Insanity one stage further- Do the Orange Bit, then put Sub USDAToDBArraysThirdWB() back as it was, save, close, open the file.. run Sub USDAToDBArraysThirdWB(), and it is no longer slow !! AARRRGGGHHHH . ..and the orange phenomena does not always work.... I am losing the will to live : (