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

**Doc.AElstein** · 09-25-2016, 08:21 PM

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