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
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