I'm trying to come up with a formula that sets a range based on whether the
rows of potential data is a Y (User can enter Y or N in Column A). There are
only 10 data rows at the moment, but it could grow a few more.

This is what I have in J2, which doesn't show anything, but it does not
change the range, it just omits that number in the range, i.e., 4:
=IF(A2="Y",RANK(I2,$I$2:$I$9,-1),"")

Column I contains the data I want to rank, but only if the value in the
corresponding cell in the same row in column A is "Y".
I'd not like to use a macro if possible.
Cam anyone help? Thanks!