lundi 3 août 2015

How to avoid using too many if statements in VBA?

I want to exclude blank rows from all my ComboBoxes in a form, but can't find a practical way of doing so. I came up with this example:

Dim Foo as Worksheet: Foo = Sheets("Foo")

With UserForm
    For i = 0 To Foo.UsedRange.Rows.Count
        If Foo.Cells(i + 1, 1) <> "" Then
            .ComboBox1.AddItem (Foo.Cells(i + 1, 1))
        End If
        If Foo.Cells(i + 1, 2) <> "" Then
            .ComboBox2.AddItem (Foo.Cells(i + 1, 2))
        End If
        If Foo.Cells(i + 1, 3) <> "" Then
            .ComboBox3.AddItem (Foo.Cells(i + 1, 3))
        End If

        [etc. etc.]

    Next

End With

I have plenty of ComboBoxes in my form, and all above statements look the same. There must be a more efficient way?

Aucun commentaire:

Enregistrer un commentaire