Creating dynamic range for VBA Combobox

1
votes

Currently I am making user form on Excel VBA and using combobox. I have range(A2:A61) named "Division" in which was included divisions of company. When I add this data into combo row source, I put named range - Division. But on the other hand divisions are dynamic, I mean new divisions are created during the year.

The problem is when I created new division I can see its name on Cell A62 but it doesn't include range - named division. As the result I can't see updated combobox list.

Firstly I tried to choose range as (A:A) and called it division. In this case I can see updated combobox list but the blank cells within range makes other problems for me.

Secondly I had this code and I tried to use it as Row Source for combobox but came out error.

Set Division = Worksheets("DataCMB").Range(Range("E2"), Range("E1048576").End(xlUp))

Please, help to find out the issue.

3
excelvbacombobox
Range("E2") and Range("E1048576") are linked to the active sheet, not to Worksheets("DataCMB") - Vincent G
in Set Division = ..., Division is not a named range, it is a Range variable. - Vincent G
If you want to modify a named range in vba, look for the Names collection - Vincent G

3 Answers

1
votes

You can insert your A2:A61 as table and define as "Division" name. So when you add new data, new data will auto include into "Division"name.

1
votes

The first problem you have, and some of the other answers here have, is that you are not telling your 2nd and 3rd Range calls which sheet they should reference, so in the line below you can see I have added Worksheets("DataCMB") in front of them. Without that it will use the ActiveSheet which may not be set to DataCMB, so it will be looking for a range that is on a different sheet, and not find it.

The other problem was that your .End(xlUp) was working from a single cell and not a range, so I have changed it to look at the whole column and to look down instead of up. So the line below will give you the whole range of whatever is in that column, but will not include any blanks at the bottom, nor the header.

Set Division = Worksheets("DataCMB").Range(Worksheets("DataCMB").Range("E2"), Worksheets("DataCMB").Range("E2:E1048576").End(xlDown))
0
votes
Private Sub UserForm_Initialize()
  Division = Range(Range("A2"), Range("A2").End(xlDown)).Address
  Me.ComboBox1.RowSource = Division
End Sub