Use VBA to make reference to last row of data

[RLloyd]

I have a sheet "System Data" with a few thousand rows of data across dozens of columns. I have another sheet ""Stats" with 40 Sumproduct (SP) formulas, each parsing multiple points of data. The SP formula can take 20 minutes to render, which is painful to sit through. I suspect SP looks through the entire million+ rows in the sheet, rather than only the rows with data. I would like a VBA solution that would create a new sheet that has the reference for the last row of data in System Data column "H", which I can use in the SP formulas. But if [you] think the constant references to the last-row reference in new sheet would also create a substantial lag, then I'm open to a a better way I have not considered.

Thank you. R Lloyd

 

[Kaper]

Excel formulas engine has built in mechanisms to work only on "used" part of spreadsheet. And this optimization works better and better with recent versions.

So there is a big chance that your formulas are simply rather complicated, and resource hungry.
Sometimes (as opposite to common sense) splitting complicated formulas into simpler ones with some partial results stored in helper columns help speeding workbook up signifficantly.
Especially if there are parts of formulas, which are repeated within the same or different formulas.

PS. As for last row via VBA in your data layout, I'd probably recommend: https://www.rondebruin.nl/win/s9/win005.htm

 

[RLloyd]

Thanks for the advice.