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

Originally Posted by Doc.AElstein

_ I cannot figure out why VBA is throwing a memory Paddy after referencing ( presumably [color = blue]ByVal[/color] ) the Long Range Row Object in the .Match. After all it is referencing the same Object.

Indeed- this is the part that is most interesting to me.

I am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. Thus at worst there should be 18 blocks of memory used. My testing leads me to believe that this is not happening with your code but I have not as yet determined the reason for that- assuming my belief is correct, of course.

Originally Posted by xlnitwit

.. am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. Thus at worst there should be 18 blocks of memory used. My testing leads me to believe that this is not happening with your code but I have not as yet determined the reason for that- assuming my belief is correct, of course.

Interesting. I have no idea how these things work, but am still puzzled why Referencing the Range Object ( rnglongSrcRow ) so many times effects memory ( BTW I meant in the last Post to say 18 times, not once, for the 18 long Range Rows of Header Names ( Nutrition names ) )

Of course if that is the problem then , we are talking I think of theoretically up to 18 x 679238 = about 12 Million calls of those Range Objects. As it catches the header often in the first half of the rows then maybe roughly 5 Million in the practice ( we both sensibly Exit For when the match is found)

A naive idea was to continually after use Set rnglongSrcRow = Nothing

Has no effect, did not really expect it as I guessed the next Set rnglongSrcRow = Something must do similar anyway, sort or, resetting, as it were.

(_.... And just for fun I did a UsedRange ..910 Set rnglongSrcRow = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(rDBlxHed, 11), wsDBlx.UsedRange.Cells(rDBlxHed, lcDBlx))... well - I thought naively it might reset some other memory coincidentally as well as the UsedRange memory ... , Lol...
http://www.excelforum.com/showthread...t=#post4487154
_.......) .. still bombed out at about 350 rows.

Time was bit difficult to see as it sometimes has not enough memory left to display the message Box with that info. So I paste that out also for the Error handling section ending of the code. Time averaged over a few runs was 20 minutes compared with 23 minutes for no extra .UsedRange on 64 Bit Win 7 machine with Xl 2007 32 Bit -------

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

Anyway, an update.

That long last run of my code that only replaced the .Match with second argument Long Range Row with a neted For For on ta capture Array of the headers) would appear to have been erroneous from the time point of view ..
I have redone the tests, adding a timer in the codes and now have averages of

xlnitwit Code Full Arrays including building a large Array() for output of all sorted rows and pasting that out in one go. ( includes the nested 2 For Loops shown below for my fist code that worked )
Sub USDAToDBArraysxlnitwit()
32 Bit Vista Xl 2007 32 Bit ------- 38 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 29 Minutes

My very first Array code just modified to replace the .Match with second argument Long Range Row with a nested 2 For Loops,
Sub USDAToDBArraysThirdWBMtchArr() __For rDBlxHed ‘ rDBlxHed was used as vertical indicia 1 To 18 for a long Range Row of each heading row, now as the Header Array arrDBlxHead( rDBlxHead , _____ ) ,
____For nixHd ‘ nixHd new variable stepping “along” like what .Match did looking for a match – we do it “manually, stopping at condition is
______IfSearchdHeading = arrDBlxHead( rDBlxHead , nixHd ) Then
_______ 'Fill in “Horizontal” position “along” in 1 D long “row” Array

Sub USDAToDBArraysThirdWBMtchArr()
32 Bit Vista Xl 2007 32 Bit ------- 85 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 47 Minutes

So actually not too bad. That was more than what I was expecting - - The little bit of experience that I have suggested to me that pasting a long row of info out is not too inefficient.

I am still a little apprehensive of having such a massive Array, not as I do not like them, I love them, - but they are close to the current size at which my computer has refused to have anything to do with other such Arrays
Also in the case of a bomb out or crash I still have some data.

But just for completeness I will go one step further with my code and fill a big Array for final output at each loop rather than pasting the row out each time.

So_...
Sub USDAToDBArraysThirdWBarroutMtchArr()
_...this will be Full Arrays Code, that is to say, - Match Loop Header Array For For - Fill Big Output Array in each Loop - Paste that out in one go. Basically similar to that from xlnitwit, but without the many other refinements / simplifications to my original code. So basically this code is xlnitwits code with a lot of extra 1 Dimensional Array building and extra unnecessary steps with extra variable , not putting the Dik = Nothing or the Dik.RemoveAll etc... stc... _-......

Latest Code Basic code idea and description:
_.. copy my last code and change it a bit... What is missing is the bits relating to the large Output Array

_ Dimensioning a new Big Output Array, arrDblxRowHolder(), ( Variant Elements are chosen to allow me to paste out empty rather than unwanted null Strings or 0’s )

_ A Long type variable used in the count of rows in output Worksheet, set to 20 originally to be increased to 21 just before first output, is now set to 1 and will be increased at the end of the Loop for each Food Product, once the “row” in the Big output Array has been filled. Then this will mean that the Array is pasted finally towards the end of the Code at Top Left of K21

_ ' Comment out the Single “row” Array dimensioning

_ ' Comment out the filling line of the 1 Dimensional Long “row” Array just inside at the end of the
1090 ===Main Outer Food Produkts Loop
and just outside this have a new line only to be done once right at the end
1095 Let wsDBlx.Range("K21").Resize(UBound(arrDblxRowHolder), lcDBlx - 10).Value2 = arrDblxRowHolder()

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

So, I did all that

Code:
Here: _..
http://www.excelforum.com/showthread...19#post4487619
_.. and here:
http://www.excelforum.com/showthread...=1#post4487621



_ So after a while it worked. ( it was Less of a surprise this time. - It is basically a version of the xlniwit code )
Sub USDAToDBArraysThirdWBarroutMtchArr()
32 Bit Vista Xl 2007 32 Bit ------- 85 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 47 Minutes

The times are a bit longer than I expected, the many other modifications / simplifications may be a bit more significant than I thought. So I will try to look at that at more detail sometime and optimise it a bit.

Also in parallel still not giving up on the .Match ( , secondargumentLongRangeObject , ) as previous results suggested that way ( for single runs on similar sorting codes then ) was very efficient.

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

Perhaps for now I will look again at one of the parallel Ideas: _..
_... the text file solution thing
http://www.excelforum.com/excel-prog...al-arrays.html

Alan

Ps. Edit...
Just adding a short bit on the way to solution for this Thread

Originally Posted by xlnitwit

_....I am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. ....

Originally Posted by Doc.AElstein

.......still not giving up on the .Match ( , secondargumentLongRangeObject , ) as previous results suggested that way ( for single runs on similar sorting codes then ) was very efficient. .....

_ I had a last few ideas that I thought were highly unlikely to have any effect, but in my short computer career I have found it best to empirically check, as weird things can happen. Also Sod’s Law says if I do not try a particular idea , then that will be the one that would have worked.. So best I spend a lot of time to make sure I do not have a solution


I am actually Setting my Range every time in the looping, which amounts to about 5 Million times.

            For rDBlxHed = 1 To 18 Step 1 ' Further search DB Header range rows Loop....
'6a Set a new Long Row Range, starting at first header row in DBlx Worksheet
            Dim rnglongSrcRow As Range
             Set rnglongSrcRow = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(rDBlxHed, 11), wsDBlx.UsedRange.Cells(rDBlxHed, lcDBlx))
'6b check at current header row for match
            Dim MtchHed As Variant 'Variable for returned "along" ( for "down" ) for .Match of srchHed in DBlx Hed Column
             Let MtchHed = Application.Match(srchHed, rnglongSrcRow, 0) 'Indicie "along"  of srchHed  ,  in   rnglongSrcRow,  exact match stipulated ( 0 )
                If IsError(MtchHed) Then 'For no match, no action  further
                ' Do nothing
                Else ' No error, so match condition
'6c On Match fill Array for Output Row in DBlx at Mtch Position
                 Let arrDBlxRow(MtchHed) = arrNutValues(JayRow - 2) ' Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name
                 'Set rnglongSrcRow = Nothing
                 Exit For ' finally stop looking across further rows under the current Long one
                End If
             'Set rnglongSrcRow = Nothing
            Next rDBlxHed ' rDBlxHed ' Furhter Loop.....................................

_...............
I thought I should try some variations on that, to reduce the Settings. The reason I hold little hope of this working is that when you Set an Object you just “write in” a lot of “pointers” type things saying “where” the “things” ( Methods, Properties etc ) are, but in actual effect you do not store at that point a lot of big Arrays of the ranges cell Members values, the Range cell Members Colors etc.. etc.. In fact you just have a lot of references to the spreadsheet. It is this last point that normally means referencing the spreadsheet was slow, ( but for my particular example, from previous work
http://www.eileenslounge.com/viewtop...175184#p175343
I had found the .Match( , secondArgumentLongRangeObject, ) very quick..

_.......
So 3 things I thought to try

_1) Just substituting the Range in the .Match Formula direct:

'6a Set a new Long Row Range, starting at first header row in DBlx Worksheet
'            Dim rnglongSrcRow As Range
'             Set rnglongSrcRow = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(rDBlxHed, 11), wsDBlx.UsedRange.Cells(rDBlxHed, lcDBlx))
'6b check at current header row for match
            Dim MtchHed As Variant 'Variable for returned "along" ( for "down" ) for .Match of srchHed in DBlx Hed Column
             Let MtchHed = Application.Match(srchHed, wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(rDBlxHed, 11), wsDBlx.UsedRange.Cells(rDBlxHed, lcDBlx)), 0) 'Indicie "along"  of srchHed  ,  in   rnglongSrcRow,  exact match stipulated ( 0 )

No noticeable difference in the Results at all with this, .. I guess VBA is still Setting somehow the range every time.
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 330 rows at 22 Minutes

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

_2 ) Set One Range and just offset that 18 ( 17 ) times. ( In fact the offset code version
http://www.eileenslounge.com/viewtop...175184#p175343
came out particularly good, ( my guess being Excel as we “see it” is all about offsetting from one call that is Excel )

So Dim and Set right outside all Loops

' First row Long Header Row Range set outside all Loops
Dim rnglongSrcRow1 As Range '                                                 , rnglongSrcRow2 As Range, rnglongSrcRow3 As Range, rnglongSrcRow4 As Range, rnglongSrcRow5 As Range, rnglongSrcRow6 As Range, rnglongSrcRow7 As Range, rnglongSrcRow8 As Range, rnglongSrcRow9 As Range, rnglongSrcRow10 As Range, rnglongSrcRow11 As Range, rnglongSrcRow12 As Range, rnglongSrcRow13 As Range, rnglongSrcRow14 As Range, rnglongSrcRow15 As Range, rnglongSrcRow16 As Range, rnglongSrcRow17 As Range, rnglongSrcRow18 As Range
 Set rnglongSrcRow1 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(1, 11), wsDBlx.UsedRange.Cells(1, lcDBlx))

And then in the Loop for the 18 Ranges ( or 1 offset by 18 ( 17 ) different amounts )

        Dim rDBlxHed As Long 'for at each DBlx row offset
            For rDBlxHed = 0 To 17 Step 1 ' Further search DB Header range rows Loop....
'6a Set a new Long Row Range, starting at first header row in DBlx Worksheet
            Dim rnglongSrcRow As Range
             Set rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed, 0) 'Icreasing one "down at a time offset to first row Long Range Object, starting at 0 for no offset

Results for
Sub USDAToDBArraysThirdWB18RngOffsets()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 340 rows at 29........ Seconds
.. Well bugger me with a barge pole.. I am still Setting each Range, just as many times as before.. but what a difference offset makes... somehow supports my wild ideas about how Excel works. Somehow this makes Excel very efficient... It is making / forcing Excel always use the one Range .. and offsetting i expect does work very efficient as ( my theory ) everything Excel does is based on offsetting, so just changing the offset ‘values’ a bit makes no great difference to what is was doing anyway.
_.. Still does not help with the “bombing out due to ( presumably ) memory problem
_.........

_3) A very tedious idea ... set 18 specific Ranges outside all loops and go through each one. Effectively taking out the Inner most Loop For rDBlxHed = 1 To 18, and going down a list of trying 18 Ranges ( In the unlikely event that this did work, I could put those ranges in an Array ot a VBA Collection Object and then loop again. But at this stage I thought it was sensible not to add anything else that ( shouldn’t but ) might have strange unexpected effect.. ( But first I wrote a code to wipe out my code Lines_...
http://www.excelforum.com/showthread...39#post4488539
_...– I naff myself off with my own weird habits sometimes.. )

So outside all the Loops I have

' Long Header Row Ranges set outside all Loops
Dim rnglongSrcRow1 As Range, rnglongSrcRow2 As Range, rnglongSrcRow3 As Range, rnglongSrcRow4 As Range, rnglongSrcRow5 As Range, rnglongSrcRow6 ‘  etc... etc...... As Range, rnglongSrcRow17 As Range, rnglongSrcRow18 As Range
 Set rnglongSrcRow1 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(1, 11), wsDBlx.UsedRange.Cells(1, lcDBlx))
 Set rnglongSrcRow2 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(2, 11), wsDBlx.UsedRange.Cells(2, lcDBlx))
 Set rnglongSrcRow3 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(3, 11), wsDBlx.UsedRange.Cells(3, lcDBlx))
 Set rnglongSrcRow4 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(4, 11), wsDBlx.UsedRange.Cells(4, lcDBlx))
‘  etc.....etc.......
‘  ...etc........
[/size]Set rnglongSrcRow17 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(17, 11), wsDBlx.UsedRange.Cells(17, lcDBlx))
 Set rnglongSrcRow18 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(18, 11), wsDBlx.UsedRange.Cells(18, lcDBlx))[/size]

_.. and equally boring at the point where the inner most Loop is we now have 18 similar code lines

        'Go Through 18 Long range Row Objects'For rDBlxHed = 0 To 17 Step 1 ' Further search DB Header range rows Loop....
'6b check at current header row for match
        Dim MtchHed As Variant 'Variable for returned "along" ( for "down" ) for .Match of srchHed in DBlx Hed Column
         Let MtchHed = Application.Match(srchHed, rnglongSrcRow1, 0) 'Indicie "along"  of srchHed  ,  in   rnglongSrcRow1,  exact match stipulated ( 0 )
            If IsError(MtchHed) Then 'For no match, no action  further
            ' Do nothing
            Else ' No error, so match condition
'6c On Match fill Array for Output Row in DBlx at Mtch Position
             Let arrDBlxRow(MtchHed) = arrNutValues(JayRow - 2) ' Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name
             GoTo Loopy 'Exit For ' finally stop looking across further rows under the current Long one
            End If
'Repeat '6b - 6c for other 17 Long Range Row Objects
         Let MtchHed = Application.Match(srchHed, rnglongSrcRow2, 0)
            If IsError(MtchHed) Then 'For no match, no action  further
            Else ' No error, so match condition
             Let arrDBlxRow(MtchHed) = arrNutValues(JayRow - 2) ' Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name
             GoTo Loopy 'Exit For ' finally stop looking across further rows under the current Long one
            End If
‘_.. etc....etc...
_.....etc....
         Let MtchHed = Application.Match(srchHed, rnglongSrcRow18, 0)
            If IsError(MtchHed) Then 'For no match, no action  further
            Else ' No error, so match condition
             Let arrDBlxRow(MtchHed) = arrNutValues(JayRow - 2) ' Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name
             GoTo Loopy 'Exit For ' finally stop looking across further rows under the current Long one
            End If
         'End of going through 18 Long Row range Objects.'Next rDBlxHed ' rDBlxHed ' Furhter Loop....................................

Results
Sub USDAToDBArraysThirdWB18RngObjects()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 342 rows at 22....Seconds
Again a very respectable time !! ( even a bit better than 1 range with 18 ( 17 ) offsets – need to recheck a bit and average to confirm....)


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

So some very interesting results on the time side..

But still something very weird going on, apparently Excel eating up ( permanently ) a chunk of its memory for each Range Object call....

_........

Alan

p.s Updated Macro File
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

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 : (

Hi
The results from the last Post were so mental, I am encourage to keep as much away from Excel as I can in this project.. then to that end , up the idea from the end of Post #34_...
http://www.excelforum.com/showthread...=1#post4484346
_..... Here the idea is to prevent having to open the large data File, NutritionalValues2016.xlsx , those long columns are put previously in a Text File. Excel is then closed and reopen ( to clear the strange memory of memory used – one of the main findings of this thread ). Then that is got from the text File and put into 3 VBA Arrays ( those already used in the main codes discussed in the last few posts. )
Some preliminary work has already been done for that:
http://www.excelforum.com/excel-prog...al-arrays.html

_... For now, until I can get a more efficient Text File column to VBA Array Code ( Using something like ADODB stuff ) , the code I wrote here will have to do , Sub TextToVBAArray(),
http://www.excelforum.com/showthread...t=#post4484332

_.. They will be incorporated into a code of the form
Sub USDAToDBArraysThirdWB18RngObjects()

_ Briefly what is done to convert that code to_....
Sub USDAToDBArraysThirdWB18RngObjectsFrmText()

_..............is

Rem 1) was all to do with the Big data File, ( what we think is giving at least some of the memory problems regardless of whether we close it later or not – the opening of it is the problem.. ). We use that code section now to bring in the Data from the Text File .
( _..... Here is the text File I made earlier
https://app.box.com/s/q7z0vyu8fm1pd4mchgpw5f90hfbj7do9
_..and the contents are shown here:
http://www.excelforum.com/showthread...=2#post4484311
_...)
So we copy the “innards” from the Text code , Sub TextToVBAArray() , into that code section. That is to say, paste it over all up to the unique Food Produkt Diktionary section

_ That’s it really, the rest of the code stays for now the same for better comparison..

_ Done all that

Results
Sub USDAToDBArraysThirdWB18RngObjectsFrmText()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 446 rows at 46 Seconds

To compare with the similar code which required opening of large XL data File, NutritionalValues.xlsx
Sub USDAToDBArraysThirdWB18RngObjects()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 23 Seconds

The improvement does show that opening of the size of the large data File, NutritionalValues.xlsx
, was an issue. But well below the issue the mysterious memory of memory caused by the use of a Range Object in = .Match( , SecondArgumentRangeObject , )
That still dominates.

Oh well, never mind

Alan


Code here ( split to fit in, but all one code )
http://www.excelforum.com/showthread...t=#post4490364
http://www.excelforum.com/showthread...t=#post4490366
http://www.excelforum.com/showthread...t=#post4490368
http://www.excelforum.com/showthread...t=#post4490371


Also in File with macros
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

File For sorted data output
DBSept2016.xlsx
https://app.box.com/s/rr9poitdmxits6a10g7k5c7ujz0lkylf

TextFile ( Big data File no longer needed )
NutValues.txt
https://app.box.com/s/q7z0vyu8fm1pd4mchgpw5f90hfbj7do9