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?
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.