Create array

[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

 

[Tom Hutchins]

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