Variable Ranges

[Erika]

I have the following formula in my spreadsheet
=IF(SUMPRODUCT(MAX(($C$2:$C$100)*($A$2:$A$100=A2)))=C2,C2,"")
The formula works great - however when I paste information into the
spreadsheet and use this formula the ranges can change for example sometimes
the information only goes to row 10 and sometimes it goes to row 500

Is there any way to make excel recoginize the number of rows being used? I
have this in a macro to drop the formula in.

 

[T. Valko]

Use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic