Userform Combibox (dropdown) box populating

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
617
Good Morning,

I've got a data array, which my userform combibox/dropdown menu references.

I have 100 available "slots" for data input in the array, but I don't use all of them. The initialize code (below) works well for the combibox to populate it. However, being as I only use about 20 of the rows currently, and the other 80 are for future expansion, I was wondering:

Is it possible to have the combibox only populate with rows that have information? Meaning if a row is blank (the unused rows), don't show them in the dropdown?

Code:
ComboBox1.List = Application.Index(Sheets("Ports").Range("B3:G102").Value, Evaluate("row(1:100)"), Array(1, 6))
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,249
Office Version
2007
Platform
Windows
If you want to load data from row 3 to the last row with data according to column B, it can be like this:

VBA Code:
Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("Ports").Range("B" & Rows.Count).End(xlUp).Row
  ComboBox1.List = Application.Index(Sheets("Ports").Range("B3:G" & lr).Value, Evaluate("row(1:" & lr - 2 & ")"), Array(1, 6))
End Sub


But if you have blank intermediate cells, then it could be like this:

Code:
Private Sub UserForm_Initialize()
  Dim sh As Worksheet, i As Long
  Set sh = Sheets("Ports")
  For i = 3 To 102
    If sh.Range("B" & i).Value <> "" Then
      ComboBox1.AddItem sh.Range("B" & i)
      ComboBox1.List(ComboBox1.ListCount - 1, 1) = sh.Range("G" & i)
    End If
  Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,230
Messages
5,413,211
Members
403,466
Latest member
sammv

This Week's Hot Topics

Top