Array Formulas with multiple rows
 

I hope someone can help me. I have the following array formula:

ActiveCell.FormulaArray = "=SUM(($A1:$A2416="" Current
VACANT"")*(E1:E24160))+SUM(($A1:$A2416="" Vacant
2005"")*(E1:E24160))"

This works fine if my worksheet is only 2416 rows. However, the amount
of rows in the workbook may change. I want it to be able to recognize
all the rows with actual data.

I have tried to change $a2416 to $a2500, e2416 to e2500 and so on, but
I get nothing. Is there a reason for this? I have this formula
inserted in row 2450, if that makes any difference.

Any help is appreciated.

Thanks