Macro; Hide columns where row <> 1 (excel 2003)

Please open the excel file first.  The macro is running a bit strange:  scenario 1 should go all the way to column V, scenario 2 to column U, scenario 3 to column M, and scenario 4 should show all the columns in the workbook - try switching among the scenarios and see if this happens.  Scenarios can be changed en cell A9.

The macro below already hides the columns where row <> 1.  What could be added to the macro first "shows"/unhides all columns, then it hides the columns.  Because I will be chaning which columns will show and hide, I need to ensure that the macro "resets" itself.

Thanks,
JP     macro-hide-columns-not-equal-to-.xls
easycapitalAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Is this what you are trying?

Sub HideCols()
    Dim LastC As Long
    Dim Counter As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        .Cells.EntireColumn.Hidden = False
        
        Select Case .Range("A9").Value
        
        Case 1
            LastC = 22 '<~~ Col V
        Case 2
            LastC = 21 '<~~ Col U
        Case 3
            LastC = 13 '<~~ Col M
        Case 4
            '~~> ShowAll
            .Cells.EntireColumn.Hidden = False
            Exit Sub
        End Select
        
        For Counter = 1 To LastC
            .Cells(1, Counter).EntireColumn.Hidden = (.Cells(1, Counter) <> 1)
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
End Sub

Open in new window


Sid
0
 
easycapitalAuthor Commented:
I "borrowed" one of your lines from above and added it to my existing code.

>>  .Cells.EntireColumn.Hidden = False

It seems to be working fine now.

Thanks,
JP



 
Sub HideCols()
    
    Dim LastC As Long
    Dim Counter As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        .Cells.EntireColumn.Hidden = False 'Show all columns

        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(1, LastC + 1), .Cells(1, .Columns.Count)).EntireColumn.Hidden = True
        For Counter = 1 To LastC
            
            'if you meant "hide where cell <> 1" then use:
            
            .Cells(1, Counter).EntireColumn.Hidden = (.Cells(1, Counter) <> 1)
            
            ' if you meant "hide where cell does not contain 1" then use:
            
            '.Cells(1, Counter).EntireColumn.Hidden = (Not .Cells(1, Counter) Like "*1*")
            
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
easycapitalAuthor Commented:
I borrowed one line, that I mentioned.
Thanks,
JP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.