populate comboboxes multicolumns ignoring empty filds

[white_flag]

Hello :)

I would like your help. so, I have an data sheet with some tables.

header 1|%|%| table1 with data
data1, data2 etc
header 2|%|%| table2 with data
data1, data2 etc
header 3|%|%| table2 with data
data1, data2 etc

then I have an combobox that is populated with the header 1, header 2, header3 ignoring the empty fields. I do not know how to make an multicolumns combobox for the second row and third row in the same combobox.

attach it is the example. thanx for looking and for help

[Bob Phillips]

What exactly do you want to see in the listbox?

[white_flag]

the next 2 rows (2, 3)

[white_flag]

The header + number from row 2 + number from row 3

[Tinbendr]

Try this.
With ComboBox1
For Each rngNext1 In myRange1
If rngNext1 <> "" Then
.AddItem rngNext1
.Column(1, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 1).Value
.Column(2, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 2).Value
End If
Next rngNext1
End With

[white_flag]

thx, Tinbendr. It is working
one extra thing:

how can be tell to excel that to take the ranges from Sheet3 (where the data stays)

'Set wsSheet = ActiveSheet
Set wsSheet = ActiveSheet.Worksheets("Sheet3")
With wsSheet
Set rngNext1 = Worksheets("Sheet3").Range("A5").End(xlUp).Offset(1, 0)
End With
rngNext1.Select
Set myRange1 = Range("a59", rngNext1)

'populate Group combobox list

With ComboBox1
For Each rngNext1 In myRange1
If rngNext1 <> "" Then
.AddItem rngNext1
.Column(1, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 1).Value
.Column(2, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 2).Value
End If
Next rngNext1
End With


like this, it is not working

[Tinbendr]

like this, it is not working
Please be specific in your reply. This doesn't tell me anything.

I cleaned up this code quite a bit. I realize that you're just trying to get it to work, and cleanup may come later, but clutter can be a frustrating thorn-in-the-side.

Quickly, a few comments.

The use of Select is usually unnecessary. The macro recorder uses it exclusively, but it really in a inefficient way to deal with code.

Using a single statement in a With nest is unnecessary. Just define it and go.

You can reuse variables, but it's a bad practice. It'll usually come back to haunt you later. While the reuse of RngNext1 is possible, you should specifically reset it to Nothing before reassigning it to a different task. Better still is to use a different variable with a descriptive name so you can understand your code (especially in the future.)

Finally, I'm still unsure about the Range of data. Is the data at A5:A59, or is there some dynamic element I'm unaware? If the range is fixed, then just define it as so and let the If/Then filter any empty cells.

Private Sub UserForm_Initialize()

Dim wsSheet As Worksheet
Dim LastCell As Range
Dim rngNext1 As Range
Dim myRange1 As Range

Set LastCell = Worksheets("Sheet3").Range("A5").End(xlUp)
Set myRange1 = Range("a59", LastCell)

'populate Group combobox list
With ComboBox1
For Each rngNext1 In myRange1
If rngNext1 <> "" Then
.AddItem rngNext1
.Column(1, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 1).Value
.Column(2, ComboBox1.ListCount - 1) = rngNext1.Offset(0, 2).Value
End If
Next
End With

End Sub

[white_flag]

thx for pointing some issues.
Subscript out of range..for me it is clear but for excel it is not (your code). The excel consider this, that it is not an valid range. the range data will be dynamic (for the moment I think it is better)