1

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))).

Improve this question
3
  • 1
    see: stackoverflow.com/questions/11169445/…
    – Scott Craner
    Commented Jan 30, 2020 at 21:26
  • @ScottCraner While your XY comment is spot on, I'm actually interested in an answer to the question as well...
    – Alex M
    Commented Jan 30, 2020 at 21:27
  • @AlexM if you want to do array formulas in vba, use Worksheet.Evaluate. outside a few exceptions, it is better to use native vba.
    – Scott Craner
    Commented Jan 30, 2020 at 21:32

0

Reset to default

You must log in to answer this question.