Hide Columns Depending on Result of a Cell

Excelor

New Member
Joined
Sep 5, 2014
Messages
18
Okay, I am new to a lot of this, so this may be simple.

I am looking for a formula or code that will hide columns depending on the content of a certain cell.

I found some code on an earlier post but that only worked when the cell was selected and manually changed.

What I have is a cell, say L1, that has a formula which based on the result of other cells will either return a "Y" or an "N". When this returns an "N" I want columns D:I to be hidden. When it is a "Y" the columns will be visible.

Hopefully that makes sense, hope someone can help!!

Thanks
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Something like this, pasted into the sheet module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Columns.EntireColumn.Hidden = False
If Range("L1").Value = "N" Then Columns("D:I").EntireColumn.Hidden = True
End Sub
 
Upvote 0
Welcome to the Board!

For a formula you can use the Calculate event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> rng1 <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = Range("L1")<br>    <SPAN style="color:#00007F">Set</SPAN> rng1 = Range("D:I")<br>    <br>    Cells.EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> rng.Value = "N" <SPAN style="color:#00007F">Then</SPAN><br>        rng1.EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        rng1.EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,
 
Upvote 0
Try this:
Right Click on sheet tab
Choose View code
Paste the below code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("L1") = "N" Then
Worksheets("Sheet1").Columns("D:I").Hidden = True
Else
Worksheets("Sheet1").Columns("D:I").Hidden = False
End If
End Sub
 
Upvote 0
Excelor,

Welcome to Mr Exel.

The following code will fire as the changes are made in your cell. Paste the code in the sheet module relevant to the sheet you want the changes to happen.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Sheets("Sheet1").Columns("D:I")
    rng.EntireColumn.Hidden = False
If Range("$L$1").Value = "Y" Then
rng.EntireColumn.Hidden = True
End If

End Sub

hope that helps,

FarmerScott
 
Upvote 0
@Scott
This line needs to be "N"
Code:
If Range("$L$1").Value = "Y" Then
 
Upvote 0
Thanks Michael for the catch. Putting on the golf gear today?
 
Upvote 0
Yep.....had a 72 today....on the way back, Yay !!!
 
Upvote 0
Thanks for all the replies! I will get on it later and let you know how I get on.

Major beginner here, so you will probably see a lot more coming from this one!

Cheers again!
 
Upvote 0
Something like this, pasted into the sheet module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Columns.EntireColumn.Hidden = False
If Range("L1").Value = "N" Then Columns("D:I").EntireColumn.Hidden = True
End Sub

This works perfectly. I thought at first it wasn't but then when I clicked into any cell on the Sheet it hid/showed the columns accordingly. Just what I needed. I take it this fires whenever any cell is selected on the given Sheet right?

Can I ask aswell, this didn't seem to work when I was using Excel 2007 at home. Was it maybe something I was doing wrong or will it not work in certain versions of Excel?

Thanks again,

Mike
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top