Why Range name not showing in list
 

I have been studying creating range names in VBA. I have two procedures that
work (shown below.) When the first example is run, the range name does not
display in the toolbars Name Box but the second one does. I don't understand
why and if I create the name using the first method, how can I get it to show
up in the Name box list?

1st Example
ActiveWorkbook.Names.Add Name:="ListTbl", _
RefersTo:="=Offset(Sheet2!$A:$A,0,0,COUNTA(Sheet!$ A:$A),4)"

2nd Example

€˜ Sheets("ListTbl").Activate
'
' With ActiveWorkbook.Worksheets("ListTbl ")
' Set rng = .Range("$A$2")
' Set DestCell = rng.End(xlDown)
'
' Do
' If DestCell = rng.Row Then
' 'stop looping, at the top of the range
' Exit Do
' End If
'
' If DestCell < "" Then
' Exit Do
' Else
' 'come up a row and continue testing
' Set DestCell = DestCell.Offset(-1, 0)
' End If
' Loop
'
' .Range(rng, DestCell).Name = "Sheet2"


' End With
Thank you very much

Why Range name not showing in list
 

That's the way excel works. Those kinds of names that have that kind of formula
never show up in the namebox.

But there are lots of limitations to that namebox (width is one) and even the
Insert|Name|Define dialog.

You may want to get this addin:
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager
NameManager.Zip from http://www.oaltd.co.uk/mvp

Billy B wrote:

I have been studying creating range names in VBA. I have two procedures that
work (shown below.) When the first example is run, the range name does not
display in the toolbars Name Box but the second one does. I don't understand
why and if I create the name using the first method, how can I get it to show
up in the Name box list?

1st Example
ActiveWorkbook.Names.Add Name:="ListTbl", _
RefersTo:="=Offset(Sheet2!$A:$A,0,0,COUNTA(Sheet!$ A:$A),4)"

2nd Example

€˜ Sheets("ListTbl").Activate
'
' With ActiveWorkbook.Worksheets("ListTbl ")
' Set rng = .Range("$A$2")
' Set DestCell = rng.End(xlDown)
'
' Do
' If DestCell = rng.Row Then
' 'stop looping, at the top of the range
' Exit Do
' End If
'
' If DestCell < "" Then
' Exit Do
' Else
' 'come up a row and continue testing
' Set DestCell = DestCell.Offset(-1, 0)
' End If
' Loop
'
' .Range(rng, DestCell).Name = "Sheet2"


' End With
Thank you very much

--

Dave Peterson