asked on

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

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

easycapital

ASKER

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

easycapital

ASKER

I borrowed one line, that I mentioned.
Thanks,
JP

Macro; Hide columns where row &lt;&gt; 1 (excel 2003)

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