Named Dynamic Range for every other row

**ShredDude** · 09-04-2010, 06:01 PM

I'm wondering if it's possible to use a Named Range formula to create a noncontiguous range of every other row?

For example, if I wanted to refer to the odd #'d rows in a column of numbers, could I create a named Range for that?

This is an offshoot of another challenge in a recent project. I needed to find the min number in a column of numbers looking at every other row. While straight forward in VBA, I finally got it to work in an Array formula with a combination of MIN, ISODD, IF, and INDEX.

I'm trying to simplify that approach by pre-establishing a range reference to, for example, all the Odd # rows in a range, but am not having any luck keeping it contained to formulas.

For example in VBA you might do something like this to create a Range variable consisting of every other row.

Public Sub testRange()
Dim rngList As Range
Dim rngOddRows As Range

Set rngList = Range("test")
Set rngOddRows = rngList(1)
For r = 3 To rngList.Rows.Count Step 2
    Set rngOddRows = Union(rngOddRows, rngList(r))
Next r
Debug.Print rngOddRows.Areas.Count

Set rngList = Nothing
Set rngOddRows = Nothing

End Sub

Can we do this with a Named Range somehow? I don't seem to be able to enter an array formula in Name Manager.

Anyone have any ideas?

Thanks in advance...

Shred

**Richard Buttrey** · 09-04-2010, 06:28 PM

Hi,

Just add the instruction

rngOddRows.Name = "mynamedrange"

after you exit the loop.

HTH

**ShredDude** · 09-04-2010, 10:48 PM

Wow! I had no idea that it would survive in the list of Named Ranges in Name Manager after the Sub was over.

Learned something new today. Thank you!

I did as you suggested, and sure enough, it creates a range that survives in the workbook. Tested it out with 2-D range as follows and it works great.

Public Sub makeNamedRange()
Dim rngList As Range
Dim rngOddRows As Range
Dim iCols As Integer

Set rngList = Sheets("sheet1").Range("thelist")
iCols = rngList.Columns.Count
Set rngOddRows = Range(rngList(1, 1), rngList(1, iCols))

For r = 3 To rngList.Rows.Count Step 2
    Set rngOddRows = Union(rngOddRows, Range(rngList(r, 1), rngList(r, iCols)))
Next r

rngOddRows.Name = "OddRows"

Set rngList = Nothing
Set rngOddRows = Nothing
End Sub

I'm happy to know how to do this, as I can see some applicability for it. However, I haven't gotten it to serve the objective I began going down this path for.

I was seeking a way to simplify the retrieval of a minimum value from every other row, and thought that having a named range that called out the Odd #'d rows for exmaple might be helpful.

In my earlier project, I achieved this objective on a 2D range with a formula like the one below to find the min value of the odd #'d rows in the first column of the range:

{=MIN(IF(ISODD(ROW(theList)),INDEX(theList,,1)))}

Trying to adapt this to my new named range ODDROWS, I quickly realized it has Areas and not rows, and so resorted to the longer syntax of INDEX that refers to the Area, but to no avail. It tried this Array Formula in an effort to find the min of the first column across all Areas: