Results 1 to 4 of 4

Thread: New to Loops

  1. #1
    Join Date
    Mar 2011
    Posts
    28

    Unanswered: New to Loops

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

  3. #3
    Join Date
    Mar 2011
    Posts
    28
    thanks colin

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •