Hi again,

Slight update - the code didnt work perfectly, it just appeared to
work perfectly. What is was doing was placing a formula in the named
ranges list, but it actually didnt refer to anything.

Anyway, here is the code that works:

Sub GenerateNamedRanges(TargetSheet As String)
Dim S As Worksheet
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Dim Referral As String
Application.Volatile

Set S = ThisWorkbook.Worksheets(TargetSheet)
S.Activate

Set WorkRange = S.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)

CellCount = WorkRange.Count

For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
Referral = "=" & S.Name & "!R" & WorkRange(i).Row + 1 &
"C" & WorkRange(i).Column & ":"

Referral = Referral & "INDEX(" & S.Name & "!C" &
WorkRange(i).Column & ":" & "C" & WorkRange(i).Column & ","

Referral = Referral & "COUNTA(" & S.Name & "!C" &
WorkRange(i).Column & ":" & "C" & WorkRange(i).Column & "))"

ThisWorkbook.Names.Add Name:=WorkRange(i).Value,
RefersToR1C1:=Referral, Visible:=True
End If
Next i
End Sub

This will look at the first row of a worksheet, treat each cell with a
value in it as a range to be defined and use the cell value as the
name, and then pump the formula into the named range list for each
column. The named ranges are dynamic.

So in short - each column with a list of values starting in the
topmost cell will have a dynamic named range defined, the name of the
range will be whatever value is in the topmost cell, and the range is
dynamic starting from the second cell in the column and reaching down
to the last used cell.

Cheers

The Frog