Multiple Ranges in 1 Column using VBA

N

neil.aitkenhead

I am trying to create multiple ranges in one column using VBA.

I have a set of data in one column and I want to create ranges for each
value, eg

Data:
A
A
A
B
B
C
C
C

I would like a range for A, a range for B, a range for C etc

Thanks
Neil
 
O

Otto Moehrbach

This macro does what you want. I assumed that your data is in Column A
starting in A2. Note that your data must be sorted before you run this
macro. Each range is named the value of one of the cells with "Name" after
it. In other words, if the cell value is Baker, the range name assigned is
BakerName. HTH Otto
Sub SetRngs()
Dim First As Range
Dim Last As Range
Dim c As Long
c = 1
Set First = Range("A2")
Do
If First.Value <> First.Offset(c).Value Then
Set Last = First.Offset(c - 1)
If Not IsEmpty(First.Value) Then _
Range(First, Last).Name = First.Value & "Name"
Set First = Last.Offset(1)
c = 1
Else
c = c + 1
End If
Loop Until IsEmpty(First.Value) = True
End Sub
 
Top