1 Attachment(s)
Solved: create a named range with a changing area
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
