Name range based on Cell content
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
|