CODEWITHSUNDEEP
excelvbalistcomboboxmultiple-columns
Lawrence231
Lawrence231

Reputation: 31

VBA comboBox multicolumn remove blank row and specific value listed

I have a comboBox which list two columns (A and H). The conditions to list the items are: 1. Add items who doesn't content blank row from the column A 2. Add items who aren't equal to zero for the column H

I was able to perform the first condition with this code:

Private Sub UserForm_Activate()

Dim currentCell As Range

With ComboBox1

.ColumnCount = 2
.ColumnWidths = "70;30"
.ColumnHeads = False
.BoundColumn = 1

With Worksheets("Sheet")
    With .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each currentCell In .Cells
            If Len(currentCell) > 0 Then
                With Me.ComboBox1
                    .AddItem currentCell.Value
                    .List(.ListCount - 1, 1) = currentCell.Offset(, 7).Value
                End With
            End If
        Next currentCell
    End With
End With
End With


End Sub

I tried to change that part for the second condition, it doesn't work:

With Worksheets("Sheet")
    With .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each currentCell In .Cells
            If Len(currentCell) > 0 & currentCell.Offset(, 7).Value <> 0 Then
                With Me.ComboBox1
                    .AddItem currentCell.Value
                    .List(.ListCount - 1, 1) = currentCell.Offset(, 7).Value

Thank you

Upvotes: 1

Views: 108

Answers (2)

Mika Oukka
Mika Oukka

Reputation: 216

In your second condition, all you need to do is to replace the "&" with "And" to make it work. I would also avoid too many nested With's here. Maybe something like this:

Dim myRange As Range
Dim mySheet As Worksheet
Dim currentCell As Range

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet3")
    Set myRange = Range(.Cells(2, 1), .Cells(lastRow, 1))
End With

With ComboBox1
    .ColumnCount = 2
    .ColumnWidths = "70;30"
    .ColumnHeads = False
    .BoundColumn = 1

    For Each currentCell In myRange
        If Len(currentCell) > 0 And currentCell.Offset(, 7).Value <> 0 Then
            With Me.ComboBox1
                .AddItem currentCell.Value
                .List(.ListCount - 1, 1) = currentCell.Offset(, 7).Value
            End With
        End If
    Next currentCell
End With

Upvotes: 1

Naresh
Naresh

Reputation: 3034

Private Sub UserForm_Initialize()
Dim Sh As Worksheet, rng As Range, arr(), cL As Range
Set Sh = ThisWorkbook.Worksheets("Sheet1")

'Make union of cells in Column A based on the two conditions given
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Sh.Range("A" & i).Value <> "" And Sh.Range("H" & i).Value <> 0 Then
        If rng Is Nothing Then
        Set rng = Sh.Range("A" & i)
        Else
        Set rng = Union(rng, Sh.Range("A" & i))
        End If
    End If
Next

'Make array of values of rng ang corresponding H Column cells
ReDim arr(rng.Cells.Count - 1, 1)
i = 0
For Each cL In rng
    arr(i, 0) = cL.Value
    arr(i, 1) = cL.Offset(0, 7).Value
    Debug.Print rng.Cells(i + 1).Address; arr(i, 0); arr(i, 1)
i = i + 1
Next

'Assign the array to the ComboBox
ComboBox1.ColumnCount = 2
ComboBox1.List = arr

End Sub

enter image description here

Upvotes: 0

Related Questions