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

Hello

I just read some of what I said in the last Post, .... some of it could be bollox.
.. or so I thought
The bit about Excel having a non clearable record after every long line paste may have been OK,

But i am not ( yet ! ) convinced that the Range Object as second argument in a .Match is necessarily a bad idea. But I could be wrong, - I am not interested in so much in what I end up doing but the Why and What is happening is important to me.

So to that end,- current experiment
Background.
I always used to build up a big final Output Array within, as it were, VBA to then paste out in one go. In answering many Sorting Threads I still do. I had gone a bit off it with my own work as I have worksheets I use daily for me and my Wife’s Diets that are from about 2 – 4 times as big as the final Output Array here. I have experienced Problems , that is to say it just don’t work capturing and pasting out Arrays of those size. ( In addition when I modify or add in one bit of stuff we eat, it is better to just update a row and then add the increased Nutritional values to to the current total etc.. etc..
In any case I got a bit side tracked from the idea of filling a large Array for output as an alternative for this particular problem.

So what to do for this post
As a first step I will simply modify my_..
Sub USDAToDBArraysThirdWB()
_...just so as to fill a big Array for output. See what happens then take it from there...

So the key to it working, I am ( was ) thinking is this bit from xlnitwit code: ( and the subsequent one off pasting of it at the end )

Dim arrDblxRowHolder() As Variant
ReDim arrDblxRowHolder(1 To UBound(UniqueFoodNamesList) + 1, 1 To lcDBlx - 10)

I think I might like to keep this a Variant, as I did my Long Row Output Row Array, arrDBlxRow() as I prefer to leave Empty Cells as Empty, rather than them converting to vbNullString for strings, ( worse, - 0 , for Numbers – as that can really screw up average calculations when = are included rather than, as required, blanks being ignored )

My Row Output Row Array,arrDBlxRow() Dim and Re Dim is now commented out. Note I will not now replace it at that point with Dim and ReDim arrDblxRowHolder(). The reason for this being that arrDBlxRow() was deliberately REDim ed without Preserve at the start of the =Main Outer Food Produkts Loop=
‘4a
so as to empty it for the next rDBlx ( the for Output Worksheet , in DBSept2016.__ File ) . rDBlx had defined the “K” & rDBlx output Top Left for each Long Row Output Row Array, arrDBlxRow(). Correspondingly it started at 20, being incremented just before the_...
“K” & rDBlx = arrDBlxRow()
_..bit.

Now in the new code and as in xlnitwit's rDBlx is free to use for our “row” in the big Output Array, arrDblxRowHolder() . A good point somewhere back up in the code to Dim ( and Re Dim only once now ) for arrDblxRowHolder() would be , for example , in the
490 Rem 2) Dogs Bollox Workbook
_.. like what xlnitwit did. There we have lcDBlx, the Last column in the for Output Worksheet , in DBSept2016.__ File and just before this at
‘1f ) we have the unique Diktionary Keys ( Unique Produkt names effectively ) and can get the total then for output data row number from something of the form, pseudo,
UBound(arrKeys() = Dik.Keys())-LBound(arrKeys() = Dik.Keys()) +1
So
522 Dim arrDblxRowHolder() As Variant
524 ReDim arrDblxRowHolder(1 To UBound(arrKeys()) + 1, 1 To lcDBlx - 10)

So for this simply first Big Output Array example we need to comment out the Long Row Output Row Array Paste line which 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()
which is exactly what xlnitwit did.

All that remains now is to modify the central bit of the code to add things to this Array rather than the Long Row Output Row Array, arrDBlxRow(). So the Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name as before, but also now at the appropriate rDBlx ( rDBlx used now as starting at 1, - our intention being to paste the final Array out from Top left at K21 , as seen above line 1095.

So I did all that.... ran the code...
Sub USDAToDBArraysThirdWBarrOutLongRangeRow()

Poo ! It bombed out again at 348 rows.. I did not expect that !!!

Have to think again !!

Bollox ( well at least some of what I said in the last post was not as much Bollox as I thought at the start of this post ?!? )..... Bo....

I am determined to get to the bo..ttom of this....

Later..
Alan



_._________________________________________-

_...............................................
( Code Sub USDAToDBArraysThirdWBarrOutLongRangeRow()
http://www.excelforum.com/showthread...=1#post4485268
and here
http://www.excelforum.com/showthread...66#post4485266


And in this file
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

Sub USDAToDBArraysThirdWBarrOutLongRangeRow() '
Sub USDAToDBArraysxlnitwit()
And the original ( bombing out in 32 Bit )
Sub USDAToDBArraysThirdWB()
Sub USDAToDBSpreadsheetThirdBook()