MultiDimentional Array Question

[TLA]

I can't always wrap my brain around how to effectively use Arrays. Here is my issue

I used VBA to pull a large amount of data into two tabs of a spreadsheet. Now I need to summarize that data and I'm haivng an issue. I can do it with formulas using Sumprocut but becuase the sheets have data in 25000 rows and 11 columns when I add the Summation forumlas Excel slows to a crawl.

and if I have those formulas in place on the summary sheet the Macro to pull in the data runs at 1 line every couple seconds.

So I'm trying to create a macro to pull the values into the main sheet

On the Summary Sheet I have SKUs in Column A (90 or so of them) These are not simple numbers (format examples would b 10-1001, and non sequential)

These start in row 9 and go to N (SKUs are added and removed)
In row 8 I have a header row and in Rows B - K I have locations (Loc A, Loc B, Loc C, etc)

WHat I need to do is search down the Master database tab and for each row that item has a SKU (in column B) and Location (in Column E)

I want to fill in the Grid in my Summary tab, but I can't quite figure out how to load my Array in one pass down the database So I can spit it back out by SKU by Location on the Summary tab. i Can't work how to work out what array dimension to increment when I evaluate the line.

 

[chuckchuckit]

I would first check that the screen updating is turned off "before" loading data into any cells. And then turn screen updating back on "after" all screen processing is done. Here is the code to do so:

Application.ScreenUpdating = False 'turn off screen display changes so runs in background
 
    'Run any screen updating code here
 
Application.ScreenUpdating = True 'turn back on screen display changes

If you have never done this before, you will be surprised how much faster your code runs.

 

[TLA]

I never run code without that.
However what acutally helped was changing calculation to manual and then back to automatic when i was done to stop the calculation from taking place for each line.

Still would love to know how to do that effectively with an array.

 

[chuckchuckit]

I sometimes use arrays for holding data and doing numeric processing in the array, instead of writing to worksheet cells first and doing the processing in the worksheet. Likely processing in cells really is much slower. Then when I am done with all the calcs and processing inside the array memory, I then write the final data to worksheet cells.

I did not originally do this to make it faster (even though I am sure it is), but I did it to save worksheet space.

I use arrays like they are rows and cols of a worksheet. Don't know if that is the way they should be used, but is how I use them.

I replace "Cells(r, c).value" type addresses a worksheet would use, with "MyArrayName(r, c)". I of course allocate the array for the size I will need like:

Public MyArrayName(210, 36) As Variant '210 rows, 36 columns

Then do all the processing inside the array addresses just like worsheet addresses.

Not sure if this is what you were asking about, but if you show us some snippets of your code you currently are doing, and ask how it could be modified for array use, there are many here whom I'll bet could come up with good answers about that.

Hope that is of assistance.

Chuck