VBA Macro to hide column when cell is = 4

[bebo1306]

How do I auto hide column when a specific cell is equal to 4; Cell P2 will have either a 4 or a 5, when it is equal to 4, I need column "O" to be hidden.

Thanks!

 

[pronto8285]

Sub hidecolumn()
If Range("P2").Value = "4" Then
Range("P2").EntireColumn.Hidden = True
Else
End If
End Sub

 

[MARK858]

Try this. Adapted from some code by Mr Andrew Poulsom

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$P$2" Then Exit Sub
    With Worksheets("Sheet1")
        .Columns("O").Hidden = Target.Value = 4
    End With
End Sub

The code needs to go in the worksheet module
1) Copy the code that you want to use
2) Select the worksheet in which you the code to run
3) Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
4) Where the cursor is flashing, choose Edit | Paste

 

[bebo1306]

I changed the column to "O", and that worked...But, when I change cell P2 to "5" how do I get column "O" to automatically unhide?

Thanks!

 

[pronto8285]

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("P2").Value = "4" Then
Range("O2").EntireColumn.Hidden = True
Else
If Range("P2").Value <> "4" Then
Range("O2").EntireColumn.Hidden = False
Else
Exit Sub
End If
End If
End Sub


Note: this does have to be put into the worksheet module you want this to run on.

 

[bebo1306]

Awesome!
It works perfectly...Thank you so much!!

 

[pronto8285]

No problem. I can usually help on the small issues. Its the big ones that i sometimes need to defer to some of the other more knowledgeable users on here.

 

[bebo1306]

Only problem, is that I can't utililize the "undo" command while using the macro...

 

[pronto8285]

are you simply trying to unhide the column when needed even if P2 = 4?

 

[bebo1306]

Yes...It's just ugly and in the way when not being used during a regular "4 week" month...I will just manually hide it - I have also used the If Then formula to return blanks when not being used - this helps clean it up a bit.