Speed up calculation using Macros

[Yevette]

Okay, I'm reaching out to the Excel VBA SME's!!!

I was wondering if someone could help me with VBA that I can put in Excel to put formulas in 70,000 (contiguous) cells. I have a lot of trouble getting my spreadsheet to calculate my SUMPRODUCT formula (--Sumproduct(--(A:A=A2),--(C:C="Documentation)). I need to put the result in Col. H and have the formula copied into each cell until the (A Column) data ends. When I try to do this manually (copy / paste the formula from H2, down), the processor creeps when calculating and I'm just sitting here. I heard that if the formula was done in VBA (behind the scenes) that it the process would be done almost instantly.

Clear as mud? Thanks so much for any help you can provide.

 

[West Man]

I do not know if VBA would speed it up, but if you have only 70,000 rows, why use the full colums in your sumproduct formuls. Adjust the ranges to match your data and you will eliminate unnecessay checking of a large number of cells. That alone should speed it up.

 

[Yevette]

I put whole columns in my post only as a means to cut down on putting actual row numbers - it was just an example. In my formula I am only using the range that has actual data.

 

[wigi]

With that number of rows, a pivot table would be much more interesting IMO.