VBA Macro to hide column when cell is = 4

bebo1306

New Member
Joined
Apr 11, 2012
Messages
5
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

pronto8285

Board Regular
Joined
Jul 12, 2011
Messages
87
Sub hidecolumn()
If Range("P2").Value = "4" Then
Range("P2").EntireColumn.Hidden = True
Else
End If
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try this. Adapted from some code by Mr Andrew Poulsom

Code:
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
 
Last edited:

bebo1306

New Member
Joined
Apr 11, 2012
Messages
5
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

Board Regular
Joined
Jul 12, 2011
Messages
87

ADVERTISEMENT

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.
 
Last edited:

pronto8285

Board Regular
Joined
Jul 12, 2011
Messages
87

ADVERTISEMENT

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

New Member
Joined
Apr 11, 2012
Messages
5
Only problem, is that I can't utililize the "undo" command while using the macro... :(
 

bebo1306

New Member
Joined
Apr 11, 2012
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,678
Messages
5,524,241
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top