Creating a named range in a workbook using VBA

[Guest]

I have added a combo-box control to a worksheet.
The ListFillRange for the combo-box is a named range
(DespList1!AllDepartments)
This works OK.

The problem I have is the size of the list will vary over time (get bigger).
Currently I have defined the named range to include a larger range of cells
than are actually used to accomodate growth. This means the named range
includes blank entries.

I would prefer not to have blank entries in the range because the combo-box
then shows the blank rows at the bottom of the list (and looks
unprofessional).

To avoid this i tried writing a macro to re-define the named range to the
actual list size. See code below. The macro executes without error but the
named range is not set in the workbook.

Q1 Am I trying to do something that cannot be done?
Q2 If so does anyone know of an alternative?

Macro code:

Sub SetDepartmentList()

Dim varLastRec as Variant

Sheets("Lookup").Select
Application.Goto Reference:="R65000C2"
Selection.End(xlUp).Select
varLastRec = Selection.Address
Range("A2").Select

ActiveWorkbook.Names.Add Name:="AllDepartments", _
RefersTo:="=Lookup!$A$1:varLastRec"

End Sub

 

[Guest]

Gerald,

Assuming your list is in Sheet1, starting from cell A1 going down, then you
can use this to define your named range:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), 1)