Name Manager

[utant04]

Hi All,

I have a small VBA macro that I want to use to update a names range for a combobox on a userform

However when I run the code the name range becomes blank. Looking a the Name manager it is working as the Refer To field has the correct range in, although the Value field also shows the same as the Refer To Field instead of the actual names in the cells.

My code is:
Dim EmptyRow As Long
Dim NewPerson As String
NewPerson = InputBox("Enter New Name.", "New Person")
EmptyRow = ActiveWorkbook.Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0).Row
Cells(EmptyRow, 1).Value = NewPerson
ActiveWorkbook.Names.Add Name:="Models", RefersTo:="Sheet1!$A$1:$A$" & EmptyRow

End Sub


Any help would be most welcome.

 

[shg]

You could just do this once to create a dynamic range:

  ActiveSheet.Names.Add _
      Name:="Models", _
      RefersTo:="=Sheet1!$A$1:INDEX(Sheet1!$A:$A, MATCH(REPT(""z"",255), Sheet1!$A:$A))"