use of variable in array formula

[Guest]

The following array formula is working correctly, but instead of a100 (end
limit) I have to use variable which value is changing time to time . How to
rewrite the
command to do this task?

Range("b2").FormulaArray = "=MIN(if(a2:a100>0,a2:a100))"

 

[Don Guillett]

Best would be a defined name for a self adjusting last row
or

Sub variablearrayformula()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("b9").FormulaArray = _
"=MIN(if(a2:a" & lr & ">0,a2:a" & lr & "))"
End Sub