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

--
-- Thanks in advance

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)



"GeraldM" wrote:

> 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
>
> --
> -- Thanks in advance