Replacing a Case Statement with a Loop

Question

I have this code but its hard coded. I need it to be automated in a loop or something else instead of a case statement till the empty column stops but more column will be added in a form. So the range can go from B2 to the late column in the excel sheet only if the column header is there and then it stops. It will shows the content in each column. Please note the excel sheet is called Area

Can this be done?

Private Sub ComboBox3_Change()
    Dim i As Long

    i = ComboBox3.ListIndex
    ComboBox4.Clear

    Select Case i
    Case Is = 0
        With Worksheets("Area")
            ComboBox4.List = .Range("**B2:B**" & .Range("**b**" & Rows.Count).End(xlUp).Row).Value
        End With
    Case Is = 1
        With Worksheets("Area")
            ComboBox4.List = .Range("**C2:C**" & .Range("**c**" & Rows.Count).End(xlUp).Row).Value
        End With
    Case Is = 2
        With Worksheets("Area")
            ComboBox4.List = .Range("**D2:D**" & .Range("**d**" & Rows.Count).End(xlUp).Row).Value
        End With
    Case Is = 3
        With Worksheets("Area")
            ComboBox4.List = .Range("**E2:E**" & .Range("**e**" & Rows.Count).End(xlUp).Row).Value
        End With
    Case Is = 4
        With Worksheets("Area")
            ComboBox4.List = .Range("**F2:F**" & .Range("**f**" & Rows.Count).End(xlUp).Row).Value
        End With
    End Select
End Sub

Why don't you just use 1 `With` statement encasing your case statements? not going to solve your problem but just thought I'd point that out. — K.Dᴀᴠɪs, Jan 23 '18 at 14:17
But your case syntax is incorrect. It should just be `Case 1`...`Case 2` (don't include the `Is = `) — K.Dᴀᴠɪs, Jan 23 '18 at 14:18
You don't need `Select ... Case` at all if you follow @SJR suggestion — Vegard, Jan 23 '18 at 14:24

score 2 · Answer 1 · answered Jan 23 '18 at 14:29

Something like this should work for you:

Private Sub ComboBox3_Change()

    Dim ws As Worksheet
    Dim ColNum As Long

    Set ws = ActiveWorkbook.Sheets("Area")
    ColNum = Me.ComboBox3.ListIndex + 2
    Me.ComboBox4.Clear

    If ColNum < 2 Then Exit Sub    'Nothing selected

    Me.ComboBox4.List = ws.Range(ws.Cells(2, ColNum), ws.Cells(ws.Rows.Count, ColNum).End(xlUp)).Value

End Sub

score 0 · Answer 2 · answered Jan 23 '18 at 14:30

If you want to enumerate the column number into column name use Chr(65) where 65 is the ASCII value for A.

In your case add 66 to your integer i like Chr(66+i) which returns B

Also, there is a function to convert column number into column name in excel, =SUBSTITUTE(ADDRESS(1,col_number,4),"1","") although I haven't tried it in VBA.

In your specific case, you don't need a case statement at all

Private Sub ComboBox3_Change()

Dim i As Long    
i = ComboBox3.ListIndex
ComboBox4.Clear
With Worksheets("Area")
ComboBox4.List = .Range("**E2:E**" & .Range("**e**" & Rows.Count).End(xlUp).Row).Offset(0,i).Value

End Sub

might need some tweaking since I can't test it out. But offset by 0 rows and i columns is the best solution for you

Using `CHR(66+i)` won't work for anything greater than 26 (`CHR(64+26)` returns _Z_ - `CHR(64+27)` returns _[_. `Split(Cells(1, 27).Address(True, False), "$")(0)` will return _AA_. — Darren Bartrup-Cook, Jan 23 '18 at 16:12
Using ChrW would keep to the character set that VBA actually uses (Unicode) instead of indirectly going through some unknown "ANSI" character set. — Tom Blodget, Jan 23 '18 at 17:30

Replacing a Case Statement with a Loop

2 Answers2