Solved: dynamic named ranges

[wibbers2000]

I would be greatful if anyone can assist

I have a spreadsheet that uses named ranges, however these ranges (cell references) change weekly so have to manually change the named ranges to suit.

Excel sheet attached.

What I would like to do is look at column A as the deciding factor.

So in this case I would like the first 6 row to determine the first namimg range. to offset by 3 columns and call this range casingcode, then offset by 1 column and call this range casingdesc, then offset by 1 column and call this range casingcost
then back to column A to pick up a new code of CPU and again raise 3 named ranges cpucode, cpudesc, cpucost
etc etc down column a until all products are done. However column A is dynamic in the fact the next week there may only be 5 rows for casing.

Can this sort of dynamic naming be done?

If so could some please point me in the right direction.

ps the sheet is a very small sample I have approx 1400 rows with maybe 25 different ref in column A

[Bob Phillips]

[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, 3).Resize(i - StartLine).Name = RangeName & "_desc"
.Cells(StartLine, "A").Offset(0, 4).Resize(i - StartLine).Name = RangeName & "_code"
RangeName = Replace(.Cells(i, "A").Value, " ", "_")
StartLine = i
End If
Next i
End With
End Sub
[/vba]

[wibbers2000]

XLD you are a genius...

worked perfectly

Thanks a Million