Create array

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I have a row in which I have an undefined number of merged cells (Varying in
the number of cells that each merged cell is comprised of). I now need to
populate a userform combobox with the values.

I tried doing

Me.combobox1.RowSource = "Sheet1!Q1:MN1"

but the cbo only has the first value appear. How can I get the full list of
the merged cell values into the cbo?

Thank you

QB
 
If they are really merged cells, all the data is in the first (topmost left)
cell. If the data is separated by some delimiting character, you can load the
combobox with a routine similar to this:

Private Sub UserForm_Initialize()
Dim DelimRng As Range
Dim x As Long, zzz
'identify the delimiting character
Const DelimChar = ","
'only need the first (topmost left) cell of the merged range
Set DelimRng = Sheets("Sheet1").Range("Q1")
'use SPLIT to populate an array variable
zzz = Split(DelimRng.Value, DelimChar)
'add the array items to the combobox
For x = LBound(zzz) To UBound(zzz)
Me.ComboBox1.AddItem Trim(zzz(x))
Next x
Set DelimRng = Nothing
End Sub

If you mean that your Q1:MN1 range includes several sets of merged cells,
you can load the combobox by looping through the whole range. Merged cells
are treated as a single cell. For example:

Private Sub UserForm_Initialize()
Dim SelRng As Range, c As Range
Const DelimChar = ","
Set SelRng = Sheets("Sheet1").Range("Q1:Z10")
For Each c In SelRng
If Len(Trim(c.Value)) > 0 Then
Me.ComboBox1.AddItem Trim(c.Value)
End If
Next c
Set SelRng = Nothing
End Sub

Hope this helps,

Hutch
 
Back
Top