Automatically Defining Ranges
 

Sub Test()
Dim iLastRow As Long
Dim iStart As Long
Dim sName As String
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sName = Range("A1").Value
iStart = 1
For i = 2 To iLastRow
If Cells(i, "A").Value < sName Then
Range("A" & iStart & ":A" & i - 1).Name = "X_" & sName
sName = Cells(i, "A").Value
iStart = i
End If
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I have data that is going to change every month, and need a code that I
can use to define ranges automatically within that data. There will be
some months where certain codes are in the data, and other months where
it won't be there. So I likely would need to run the macro every
month. I will present an example:

The data will have seven columns, and will be sorted by column A, which
contains the codes needed to define the ranges:

Example:

Column A
ABEHS
ABEHS
ABEHS
ABEHS
BOOOT
BOOOT
BOOOT
CAHGT
CAHGT
DOORT

So what I would need the VBA code to do is define the first four rows
as a defined range called "ABEHS", the next 3 to be defined as "BOOOT",
etc. Remember, the data changes every month - so next month, the
first 10 rows may be ABEHS...or perhaps I will have no ABEHS data at
all.