# Shortest Method To Reference A Single Row/Column of a Named Range in Excel?

I have a named range (`NamedRange1`) and I need to reference the first row and first column of that range within a formula. I came up with the following:

• First row:

`INDIRECT("R" & ROW(UnpivotSource) & "C" & COLUMN(UnpivotSource) & ":R"& ROWS(UnpivotSource) +ROW(UnpivotSource) -1 & "C" & COLUMN(UnpivotSource),0)`

• First column:

`INDIRECT("R" & ROW(UnpivotSource) & "C" & COLUMN(UnpivotSource) & ":R"& ROW(UnpivotSource) & "C" & COLUMNS(UnpivotSource) + COLUMN(UnpivotSource) -1,0)`

This seems cumbersome, especially since the Excel object model exposes `Rows` and `Columns` in VBA. Is there a more concise way of expressing this?

-

## 1 Answer

Row:

``````INDEX(UnpivotSource,1,0)
``````

Column:

``````INDEX(UnpivotSource,0,1)
``````

Note that this will only work for contiguous named-ranges, there's an index function for areas of a range, but it could get tricky if you wanted something other than the first row or column.

-
Beautiful, works great and way shorter. Thanks! – technomalogical Jun 25 '10 at 12:48
Under the hood of xl do you think if I use this technique, for say Max, it will be more efficient e.g. `Max("A:A")` compared to `Max(index(myNmdRang,0,1))` ? – whytheq Mar 24 at 9:30
@whytheq, My guess would be that for large columns `Max("A:A")` would be the most efficient. You could time and test, see here: stackoverflow.com/questions/198409/…. – Lance Roberts Mar 25 at 3:11
@LanceRoberts I added a more detailed question on S.O here: stackoverflow.com/questions/36197157/… You might like to answer as the current answer is not good enough. – whytheq Mar 25 at 9:03