New to Loops

Range · #1 (**permalink**) 03-07-11, 16:59

Code:

Private Sub CmdMon_Click()
        Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob1"
        Selection.EntireColumn.Hidden = False
        
        Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob2"
        Selection.EntireColumn.Hidden = False  

      Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob3"
        Selection.EntireColumn.Hidden = False  

 
    Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob4"
        Selection.EntireColumn.Hidden = False    

    Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob5"
        Selection.EntireColumn.Hidden = False    

    Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob6"
        Selection.EntireColumn.Hidden = False 

       Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob7"
        Selection.EntireColumn.Hidden = False
End Sub

Whats the best way to put this code into a loop? I'm new to VB code. Thanks

Colin Legg · #2 (**permalink**) 03-07-11, 17:45

Like this:

Code:

Private Sub CmdMon_Click()
 
    Dim lNum As Long
 
    For lNum = 1 To 7
 
        Application.Goto Reference:="ALL1"
        Selection.EntireColumn.Hidden = True
        Application.Goto Reference:="bob" & CStr(lNum)
        Selection.EntireColumn.Hidden = False
 
    Next lNum
 
End Sub

Range · #3 (**permalink**) 03-07-11, 17:46

thanks colin

Colin Legg · #4 (**permalink**) 03-07-11, 17:54

And you don't need to hide ALL1's column seven times, so you could move it outside the loop. You can also simplify the code by removing the Goto method and the Selection object. For example, assuming the ranges are on the activesheet:

Code:

Private Sub CmdMon_Click()
    
    Dim lNum As Long
    
    ActiveSheet.Range("ALL1").EntireColumn.Hidden = True
    
    For lNum = 1 To 7
        ActiveSheet.Range("BOB" & CStr(lNum)).EntireColumn.Hidden = False
    Next lNum
End Sub

The other thing I'll mention is that in XL2007+, BOB and ALL are valid column letters. I'm not sure from your post, but if you are using BOB1 etc as named ranges then you would probably be better off naming something else to avoid conflicts. If you are using them just as cell references (ie BOB is the column reference) then that's fine.

Internet Services

Web Development

Digital Marketing

Consumer Tech