[Excel VBA] How to Bulk Add Values to a ComboBox | Reading from a Cell Range

2025年12月26日

Background

When setting up a pulldown menu (ComboBox) on a UserForm, adding multiple options manually can be tedious.

Previously, I used to repeat the .AddItem method for every single item like this:

ComboBox1.AddItem "Tokyo"
ComboBox1.AddItem "Saitama"
ComboBox1.AddItem "Kanagawa"
' ... and so on

As the number of options increased, the code became redundant and difficult to manage. Therefore, I learned a method to add items to a ComboBox by reading them in bulk from an Excel sheet.

Solution: Reading Values from an Excel Sheet

The following code reads data from Column H of “Sheet1” and adds it to ComboBox1.

Dim lastRowH As Long
Dim i As Long

' Get the last row of column H (Column number 8)
lastRowH = Worksheets("Sheet1").Cells(Rows.Count, 8).End(xlUp).Row

' Loop from row 3 to the last row
For i = 3 To lastRowH
    ComboBox1.AddItem Worksheets("Sheet1").Cells(i, 8).Value
Next i

Code Explanation

Lines 1–2: Declare the variable lastRowH (for the last row number) and the counter variable i.
Line 4: Retrieves the row number of the last entry in Column H (Column 8) of “Sheet1”.
Line 6: Uses a For loop to iterate from row 3 to the last row.
Line 7: Adds the value of Column H (Column 8) to the ComboBox one by one.
Line 8: Moves to the next row.

Note: While you can write Range("H" & i), using Cells(i, 8) (specifying Row and Column numbers) is generally recommended for loops as it is more robust.

Key Points

Cleaner Code: Eliminates the need to repeatedly write ComboBox1.AddItem, keeping the code concise.
Easy Maintenance: If the data changes, you only need to update the Excel sheet. There is no need to modify the VBA code.
Scalability: This is especially useful when managing large datasets, such as lists of prefectures or product names.