dynamic name

[technix]

is it possible to have a range name changed based on data in it.

for example.

StCellVal = "=" & Range("D7").Address cell
ThisWorkbook.Names.Add Name:="Salespeople",_
        RefersTo:=(StCellVal), Visible:=True

instead of d7 use all the data that has data

like range("65536").end(xlup) so that the named ranged changes when the data is increased or decreased?

Derrick

 

[technix]

nevermind I was able to accomplish what I needed this way

Dim cell As Range
Dim ws As Worksheet
Dim NmRng As Range
Set ws = ThisWorkbook.Sheets("Setup")
Set NmRng = ws.Range("c2", ws.Range("c2").End(xlDown))
For Each cell In NmRng
       ListBox1.AddItem cell.Text
Next cell