Modification code VBA en boucle

bonjour

j'ai essayée de faire un code de remplissage les Combobox avec VBA mais il est très long .

vous pouvez m'aider de faire en boucle svp. Car j'ai bcp des equipements >100 et bcp des colones

merci

Private Sub ComboBox2_Change()
Dim index As Integer

index = ComboBox2.ListIndex
ComboBox3.Clear
With Worksheets("Combobox")
    Select Case index
        Case Is = 0
             ComboBox3.List = .Range("C2:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value
        Case Is = 1
               ComboBox3.List = .Range("D2:D" & .Range("D" & Rows.Count).End(xlUp).Row).Value
        Case Is = 2
               ComboBox3.List = .Range("E2:E" & .Range("E" & Rows.Count).End(xlUp).Row).Value
        Case Is = 3
               ComboBox3.List = .Range("F2:F" & .Range("F" & Rows.Count).End(xlUp).Row).Value
        Case Is = 4
               ComboBox3.List = .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value
        Case Is = 5
               ComboBox3.List = .Range("H2:H" & .Range("H" & Rows.Count).End(xlUp).Row).Value
        Case Is = 6
               ComboBox3.List = .Range("I2:I" & .Range("I" & Rows.Count).End(xlUp).Row).Value
        Case Is = 7
               ComboBox3.List = .Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row).Value
        Case Is = 8
               ComboBox3.List = .Range("K2:K" & .Range("K" & Rows.Count).End(xlUp).Row).Value
        Case Is = 9
               ComboBox3.List = .Range("L2:L" & .Range("L" & Rows.Count).End(xlUp).Row).Value

    End Select
End With
End Sub

Bonjour,

a priori, à tester :

Private Sub ComboBox2_Change()
Dim index As Integer, DL As Integer

index = ComboBox2.ListIndex
ComboBox3.Clear
With Worksheets("Combobox")
    DL = .Cells.(.Cells.Rows.Count, index + 3).End(xlUp).Row
    ComboBox3.List =. Range(.Cells(2, index + 3), .Cells(DL, index + 3)).Value
End With
End Sub

A+

Bonjour

Formidable merci bien

Résolu

BONJOUR

Private Sub ComboBox2_Change()
Dim index As Integer, DL As Integer

index = ComboBox2.ListIndex
ComboBox3.Clear
With Worksheets("Database")
    DL = .Cells(.Cells.Rows.Count, index + 3).End(xlUp).Row
    ComboBox3.List = .Range(.Cells(4, index + 3), .Cells(DL, index + 3)).Value

End With
End Sub