Userform Combibox (dropdown) box populating

[sassriverrat]

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?

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

 

[DanteAmor]

If you want to load data from row 3 to the last row with data according to column B, it can be like this:

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:

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