VBA Excel: modify dynamic named range code -

newbie question: have module, originally made roger govier.

it uses input cell header , creates dynamic named range non empty cells positioned under header. created named range labeled value of header cell.

private sub createnamedrange(header range)    dim wb workbook    dim ws worksheet    dim rstartcell range    dim rdata range    dim rcol range    dim lcol long    dim ssheet string    dim rowno long     ' table location    set rstartcell = header     set ws = rstartcell.worksheet    set wb = ws.parent    ssheet = "'" & ws.name & "'"    rstartcell       rowno = .row       set rdata = .currentregion    end    set rdata = ws.range(rstartcell, ws.cells(rowno, rstartcell.column))      set rcol = rdata.columns     lcol = rcol.column     wb.names.add name:=replace(rcol.cells(1).value, " ", "_"), _     referstor1c1:="=" & ssheet & "!" & rcol.cells(2).address(referencestyle:=xlr1c1) & ":index(c"        & lcol & ",lookup(2,1/(c" & lcol & "<>""""),row(c" & lcol & ")))"  end sub 

i want modify code instead of creating named range returns returns range of have been named range.

example: have header in a1, , data in a2:a5.

now: if call createnamedrange(.range("a1")), creates dynamic named range a2:a5.

goal: if call createnamedrange(.range("a1")) returns .range("a2:a5") variable in vba code:

dim myrange range set myrange = createnamedrange(.range("a1")) 

first thing should note subs not return value , myrange = createnamedrange(.range("a1")) not make sense (with sub; make sense function in answer).

the function below returns range, in same column input range, starting next row , including ones below until finding blank cell. range called "anyname" (and can access via range("anyname")).

private function createnamedrange(header range) range     dim currow long: currow = header.row + 1    set temprange = header.worksheet.cells(currow, header.column)    while (not isempty(temprange))      currow = currow + 1      set temprange = header.worksheet.cells(currow, header.column)    loop     set createnamedrange = header.worksheet.range(header.worksheet.cells(header.row + 1, header.column), header.worksheet.cells(currow, header.column))     createnamedrange.name = "anyname"  end function