Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a named range in a workbook using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a named range in a workbook using VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range? | Excel Discussion (Misc queries) | |||
creating a filtered range/named range | Excel Programming | |||
Creating a named range | Excel Programming | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Creating a Named Range using VB | Excel Programming |