I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim sTmp As String
Dim rng As Range
iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
sTmp = Range("E1").Value
iStart = 1
For i = 2 To iLastRow + 1
If Cells(i, "E").Value < sTmp Then
Set rng = Cells(iStart, "E").Resize(i - iStart)
rng.Name = "Range_" & sTmp
iStart = i
sTmp = Cells(i, "E").Value
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks

Name : RangeB
Refersto:
=OFFSET($E$1,MATCH("A",$E:$E,0)-1,0,MATCH("A",$E:$E,1)-MATCH("A",$E:$E,0)+1,
1)

replace "A" in the three places with "B", "C" & "D" for the other names

Regards,
Peter T


"Abdul" wrote in message
oups.com...
I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks

Name : RangeB

that should of course be
Name : RangeA

I see the Refersto string has wrapped onto 2 lines in my newsreader.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Name : RangeB
Refersto:

=OFFSET($E$1,MATCH("A",$E:$E,0)-1,0,MATCH("A",$E:$E,1)-MATCH("A",$E:$E,0)+1,
1)

replace "A" in the three places with "B", "C" & "D" for the other names

Regards,
Peter T


"Abdul" wrote in message
oups.com...
I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks

Thanks

How I could modify the below formula/code to define range another
column based on the value in column E?

for eg. if i want to name a ranges in column 9, 10 and 11 based on the
value in column E?

If Column E value is "A" then name the same rows in column 9,10, 11
etc?

thanks


Peter T wrote:
Name : RangeB

that should of course be
Name : RangeA

I see the Refersto string has wrapped onto 2 lines in my newsreader.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Name : RangeB
Refersto:

=OFFSET($E$1,MATCH("A",$E:$E,0)-1,0,MATCH("A",$E:$E,1)-MATCH("A",$E:$E,0)+1,
1)

replace "A" in the three places with "B", "C" & "D" for the other names

Regards,
Peter T


"Abdul" wrote in message
oups.com...
I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks

I'm afraid I don't follow your question. Try an understand how the dynamic
named range was made. Referring to the previous example put the following in
cells.

Type some A's in a block in col-E

A1 =MATCH("A",$E:$E,0)
A2 =MATCH("A",$E:$E,1)
B1 =A1-1
B2 =A2-A1+1

Select A1 or A2, press Shift-F3, the help on this function. Note the 0 & 1
to find the first & last values of "A" in your sorted list in col-E.

in say B5 =OFFSET($E$1,$B$1,0,$B$2,1)

Press Shift-F3 again and look at the arguments and see help on selected
function.

The value will probably return a #VALUE error if the difference between
B2-B1 is not 1.
To test, in say B6 try =COUNTA(OFFSET($E$1,$B$1,0,$B$2,1))

In another cell put it all together with the Offset and all the Match's &
the subtraction in a single formula.

In essence all dynamic ranges work the same way, a 'block' offset from an
anchor cell. In this case we used the Match function to find the first &
last rows containing A's, and a simple subtraction of the two Match's to
find the height. But depending on needs other functions might the used to
find the offset references, eg Counta to count non empty cells.

Hopefully you can make your own dynamic range (don't forget the Absolute $
signs).

Regards,
Peter T

"Abdul" wrote in message
oups.com...
Thanks

How I could modify the below formula/code to define range another
column based on the value in column E?

for eg. if i want to name a ranges in column 9, 10 and 11 based on the
value in column E?

If Column E value is "A" then name the same rows in column 9,10, 11
etc?

thanks


Peter T wrote:
Name : RangeB

that should of course be
Name : RangeA

I see the Refersto string has wrapped onto 2 lines in my newsreader.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Name : RangeB
Refersto:


=OFFSET($E$1,MATCH("A",$E:$E,0)-1,0,MATCH("A",$E:$E,1)-MATCH("A",$E:$E,0)+1,
1)

replace "A" in the three places with "B", "C" & "D" for the other
names

Regards,
Peter T


"Abdul" wrote in message
oups.com...
I have 3 sheets with variable number of rows

Column 5 contains A or B or C or D which is sorted so that all A
will
be together and all B will be together etc.

Now is there a way that i can name the range based on the column 5
content?

say i want to name RangeA for the cells haveing A's and RangeB for
the
cells haviong B''s etc?

Since the number of rows will be different i want to create dynamic
range names

thanks