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

Big List Sort. Not Enough Memory. Help Run my Code. ( Or fix it Please ! )

VBA has a Memory of the Memory it used. And I cannot clear it

Hi
I have been on this a few days now, and am going really mad!!
I am working privately on home on my old computers ( PC Vista, XL2007 ). I am trying to make a large reference list of Foods and their Nutrients to help in organising Diets.
I am hitting a Brick wall.
I do not get far before Excel “bombs out” with error “Not enough Memory”

I am keen to get to the bottom of this before I reluctantly may have to invest in a new computer, especially as I do not want to do that only to find a small improvement.


So I am asking for help here as follows.
_1) Could someone if they have access to a fairly “state of the current art” computer have a go at running a code for me and tell me how far you get. ( An Error handler section will chuck out a message as well as you will see how many rows get filled in ) . If you can give me some details of the computer you are using that will help me.

_2 ) Anyone have any other ideas how I can change the code to get over this problem. Have I missed something?. ( In parallel I will now rewrite the program without Arrays to see if that gets me anywhere.. )

So, in order for you to help me, .....
I upload two Files_....
_ “BDSept2016.xlsx” ( A couple of links to different share filing sites )
box https://app.box.com/s/1vjlvdm1n4bmlcu61f2dyxrzx9lh92oh
German Telekom: https://www.magentacloud.de/share/jqlc8w278a#$/
_... This is the File to be filled in. This is at the outset just a File with Headers ( There are 20 Rows for each Header (Nutrition Type Name ) to allow different spellings of the Nutritions and other information about the Nutritions ). So Data rows will be filled in from Row 21.
_....

_.....and data File with code ( Sub USDAToDB()
)_.......
_ “NutritionalValues2016.xlsm” ( A couple of links to different share filing sites )
box https://app.box.com/s/o77euubc1kaz11kb0ajg5o4ux8s3wi5a
German Telekom https://www.magentacloud.de/share/6t-0t1ao1v#$/
_ This has a large amount of data. Only the following Columns are of interest:
_ J Names of Food Products. ( Like Butter, Hamburger .. etc.. etc.. )
_ K Nutrition Names ( Like Fat , Protein etc.. etc... )
_ N Values of the Nutritions in standardised Units

So here the first Part of the only used Worksheet ( “NutritionalValues” ) in data File _ “NutritionalValues2016.xlsm”
Using Excel 2007 32 bit

Row\Col	J	K	L	M	N
1	Food Product	Nutrion Name			Nutrition Value
2	Butter, salted	|Protein	g	0.85	0.85
3	Butter, salted	|Total lipid (fat)	g	81.11	81.11
4	Butter, salted	|Carbohydrate, by difference	g	0.06	0.06

NutritionalValues

On average the Food Products have a bit under 80 Nutrition Values. There are 8790 Food products. There are approximately then 680000 Rows in this data File !!

So in this file the code I would like you to run for me when both Workbooks are open is.
Sub USDAToDB()
Code is also Posted here:
http://www.excelforum.com/showthread...29#post4481078
and in the following post
http://www.excelforum.com/showthread...36#post4481236
is also the same File as .xlsx, (“NutritionalValues2016.xlsx” ) should you prefer not to download a .xlsm File )
When I run this code, I, get to between 340-347 rows filled in worksheet “Tabelle1” in File “DBSept2016.xlsx” before the code errors. ( I need finally nearly 9000 !!!, so I am not getting very far.. Lol... ) ( Edit: I just managed to borrow a WINDOWS 7 XL2010 .. and only got a few more rows ?? )

Thanks,

Alan

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

Some further points:
_(i) Often , once the code “Bombs out” , the next run does not get past the first row!! So I have to close Excel and start again ??
_(ii) I added a code line to erase three large Arrays after the code no longer needed them. That had no noticeable effect.

_(iii ) My Files are very large. But I thought I had an efficient code. (_... I have gone to great lengths in Threads to optimise an efficient combination of Worksheet Functions and use of Arrays.
http://www.excelforum.com/showthread...45#post4326245
http://www.eileenslounge.com/viewtop...176056#p175302
http://www.excelforum.com/tips-and-t...ml#post4380613
I use extensively 1 Dimensional Arrays which I have found is very efficient ( at least from the Speed point of view ). _... )


_(iv) The following Info is not necessary to know to help me, but may be of interest for anyone interested the code I have posted.
Code Notes ( and Files ) to the code given Here:
http://www.excelforum.com/showthread...36#post4481236

(_... Code in this post
http://www.excelforum.com/showthread...36#post4481078
_.....)