Solved: create a named range with a changing area

[wibbers2000]

Hello,
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


[VBA]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
[/VBA]
Thanks in advance

regards
Paul

[mdmackillop]

Add this line
[VBA]
.Cells(StartLine, "A").Offset(0, 1).Resize(i - StartLine, 5).Name = RangeName & "_r"
[/VBA]

[wibbers2000]

Thanks mdmackillop...

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

thanks again