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 |
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 |
All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com