Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 10 down vote accepted

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.

share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .