Creating thousands of Named Ranges for Dependent Validation

Okay, this one has lots of issues. Sample attached. What I'm trying to achieve is to allow my users to select a position to do something with. For each Heading in my sample, I want to allow my users to use Data Validation to pull up the position numbers under that heading. I want to do it using named ranges, so need to name each column with the header name. My Sample is small; there are actually hundreds of Headers, I've just shown a few.

To bring in the position numbers under the headers, I'm first doing a Pivot of Header and Position. Next i'm copying the header range using the Advanced filter to get just the Unique values, then pasting Transpose to get them in a row. Next, I do a Vlookup to bring in the position numbers, and use IFerror to replace "" if nothing is found. Next I copy/paste as values to wipe out my formulas.


So, problems so far:
1) My position numbers have a bunch of "blanks", and I want to remove all the blanks.
2) The blanks aren't blank. When I GoTo Special, "Blanks", no blanks are found.
3) I'm using the macro below to create my named ranges, but even though the named ranges are made they aren't showing up for my data validation (using "Indirect(A1)". If I delete the named range as set up by the macro, and instead select my position numbers in the column, and name that selection with my Header name, the data validation Does work. So, my macro isn't doing what I need it to do.

Any help with this would be greatly appreciated.

Sub CreateNames()
'http://www.contextures.com/xlNames03.html

' written by Roger Govier, Technology4U
    Dim wb As Workbook, ws As Worksheet
    Dim lrow As Long, lcol As Long, i As Long
    Dim myName As String, Start As String

    ' set the row number where headings are held as a constant
    ' change this to the row number required if not row 1
    Const Rowno = 1

    ' set the Offset as the number of rows below Rowno, where the
    ' data begins
    Const Offset = 1

    ' set the starting column for the data, in this case 1
    ' change if the data does not start in column A
    Const Colno = 1


    On Error GoTo CreateNames_Error

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    ' count the number of columns used in the row designated to
    ' have the header names

    lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
    lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
    Start = Cells(Rowno, Colno).Address
    
    wb.Names.Add Name:="lcol", _
                 RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:="lrow", _
                 RefersToR1C1:="=COUNTA(C" & Colno & ")"
    wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"

    For i = Colno To lcol
        ' if a column header contains spaces,
            ' replace the space with an underscore
            ' spaces are not allowed in range names.
        myName = Replace(Cells(Rowno, i).Value, " ", "_")
        If myName = "" Then
            ' if column header is blank, warn the user and
            ' stop the macro at that point
            ' names will only be created for those cells with text in them.
            MsgBox "Missing Name in column " & i & vbCrLf _
                   & "Please Enter a Name and run macro again"
            Exit Sub
        End If
        wb.Names.Add Name:=myName, RefersToR1C1:= _
             "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
    Next i

    On Error GoTo 0
    MsgBox "All dynamic Named ranges have been created"
    Exit Sub

CreateNames_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure CreateNames of Module Technology4U"

End Sub