View Full Version : Solved: create a named range with a changing area

05-23-2009, 05:10 PM
EL XID created the code below which creates a dynamic range when the cell value in column "A" changes which works a treat. This does exactly as expected and I want to keep this intact. However you will see that it looks at the value in "a" and then tags either a _c or a _d depending on the column.

The new bit I am look for is to select the range in column B to F using the same split as listed above and name it as column "A" _r . The aim is to use this range in a vlookup

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartLine As Long
Dim RangeName As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartLine = 1
RangeName = Replace(.Cells(1, "A").Value, " ", "_")
For i = 2 To LastRow + 1

If Replace(.Cells(i, "A").Value, " ", "_") <> RangeName Then

.Cells(StartLine, "A").Offset(0, 1).Resize(i - StartLine).Name = RangeName & "_C"
.Cells(StartLine, "A").Offset(0, 2).Resize(i - StartLine).Name = RangeName & "_D"
RangeName = Replace(.Cells(i, "A").Value, " ", "_")
StartLine = i
End If
Next i
End With
End Sub

Thanks in advance


05-24-2009, 03:05 AM
Add this line

.Cells(StartLine, "A").Offset(0, 1).Resize(i - StartLine, 5).Name = RangeName & "_r"

05-24-2009, 05:34 AM
Thanks mdmackillop...

That was perfect. It seems so easy when you see the answer.

thanks again