Extract rows from range

[Svgmassive]

Is there a function/formula that will allow the extraction of only the row in the range below? thanks

Sheet1!$F$6,Sheet1!$F$10,Sheet1!$F$15,Sheet1!$F$19,Sheet1!$F$22

 

[Richard Schollar]

Hi

I'm afraid I don't understand the question - unless do you mean you have these ranges in a VBA variable and want to return the Row?

Dim r As Range
 
Set r = Range("F6")
 
Msgbox r.Row

 

[Svgmassive]

i am trying to have a dynamic range offset to a different column based on another dynamic range.I am trying to get away from using vba.The original name is empid.Empid range is.
=Sheet1!$F$5,Sheet1!$F$7:$F$9,Sheet1!$F$11,Sheet1!$F$13:$F$14,Sheet1!$F$16:$F$18,Sheet1!$F$20:$F$21

can this be done?

=OFFSET(empid,1,1,1)

 

[ukmikeb]

Hi

Yes, you can BUT I would be inclined to specify the width as well because you may get unpredictable results.

Reason being I trialled your statement on a dynamic range specified using Offset and CountA and it gave me the 19th element on row 2 of a 21 column range!

hth

 

[Svgmassive]

how would you go about doing that?

 

[ukmikeb]

Your formula -
=OFFSET(Range,rows,columns,height)

My formula -
=OFFSET(Range,rows,columns,height,width)

refer to Excel Help if you require further clarification.

 

[Svgmassive]

i tried no luck.i believe because it is a Non Contiguous Range

 

[ukmikeb]

Hi

This seems to possibly achieve what you require -

=OFFSET(INDEX(empid,1,,2),,6,1)

which will give you the cell at 6 columns offset in row 2 of your non-contiguous range.

Use the Areas option of Index to specify the number of the element in your dynamic range

hth