I have the following array formula in Excel: {=MAX((C9:C44<>"")*(ROW(C9:C44)))} that calculates and returns the row number of the last non-blank cell in a range. I could reference the result in that cell in VBA, but I want to know the methodology to calculate it without using that reference.
I've tried WorksheetFunction.Max("C9:C44" <> "") * Worksheets("Sheet1").Rows.Count, but then I'm not sure how to return it as an array result. I tried using the FormulaArray property, but I don't know if that is intended to work the way I want it to.
To re-iterate, I'm just looking for the VBA equivalent of =MAX((C9:C44<>"")*(ROW(C9:C44))).
Worksheet.Evaluate
. outside a few exceptions, it is better to use native vba.