Need a functioning range name.

[Ribbs]

I’m trying to name a range of every other cell in a column. I don’t want to merely select the cells and name the selection. There are fifty cells in the range, and it will likely change, so I need it to be dynamic.

It will be used in a macro. So far I have tried these formulas on a smaller, experimental range:

=IF(ISODD(ROW($E$7: $E$16)), $E$7: $E$16,"")

=SORT(INDEX($E$7: $E$16,SEQUENCE(COUNTA($E$7: $E$16)/2,1,1,2),SEQUENCE(1,3)),2)

=INDEX($E$7: $E$16,SEQUENCE(COUNTA($E$7: $E$100)/2,1,1,2),1)

=IF(ROW($E$7: $E$16)=SEQUENCE(10,1,2,2), $E$7: $E$16,"")

=FILTER($E$7: $E$16,ROW($E$7: $E$16)=SEQUENCE(10,1,2,2))

=FILTER($E$7: $E$16,ROW($E$7: $E$16)=SEQUENCE(5,1,2,2))

=INDEX($E$7: $E$16,2+SEQUENCE(5,1,0,2)*1)

=INDEX($E$7: $E$16,MOD(ROW($E$7: $E$16),2),1)

=INDEX($E$7: $E$16,MOD(ROW($E$7: $E$16),2)=1,1)

The first formula shows the most promise. The syntax is clear and intuitive, and it works beautifully as a worksheet formula. But, like most of the others, when I create a named range with it and then try to select the range with a macro, I get the message “Error ‘1004’ Method Range of Object Global Failed”.

The 100 cells from which the fifty are selected is named with this dynamic formula:

=OFFSET(E$7,0,1,1,(MATCH(2,1/($E$7:OFFSET($E$7,0,200,1,1)<>"")))-1)

So:

1) Can anyone suggest a formula that will work, and

2) for future reference, how can a formula that works so well as a worksheet formula fail as the definition of a range name?

Thank you for your time and the benefit of your expertise.

 

[jasonb75]

Your named range is creating an array, not a range.

Might be better to create a proper range of all rows (odds and evens) then loop through then and create a union from the odd rows.