11

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?

Lance Roberts
  • 8,563
  • 9
  • 49
  • 79
technomalogical
  • 723
  • 2
  • 7
  • 13

1 Answers1

18

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.

Lance Roberts
  • 8,563
  • 9
  • 49
  • 79
  • 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 '16 at 09: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: http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code. – Lance Roberts Mar 25 '16 at 03:11
  • @LanceRoberts I added a more detailed question on S.O here: http://stackoverflow.com/questions/36197157/is-specifying-a-column-from-a-named-range-more-efficient-than-calculating-over-a You might like to answer as the current answer is not good enough. – whytheq Mar 25 '16 at 09:03