Written by Allen Wyatt (last updated September 9, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
Excel's great conditional formatting capabilities allow you to change the formatting of cells based on the content of a cell. There is no way, unfortunately, to easily hide entire columns of data based on the value of a particular cell.
You can, however, achieve the desired effect by using a macro to analyze the cell and adjust the Hidden attribute of the row you want to conditionally hide. The following simple macro, for instance, examines the contents of cell B4 and, if the cell contains 0, hides column H. If cell B4 does not contain 0, then column H is displayed.
Sub HideColumn1() If Range("B4").Value = 0 Then Columns("H").EntireColumn.Hidden = True Else Columns("H").EntireColumn.Hidden = False End If End Sub
If you want the hiding and unhiding of the column to be done in real time, you can use the following version of the macro. Just make sure that you put this version in the code window for the worksheet on which you want it to work.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("B4").Value = 0 Then Columns("H").EntireColumn.Hidden = True Else Columns("H").EntireColumn.Hidden = False End If End Sub
Notice that the guts of the two macros are the same. The only difference is that the second version is triggered by an event within Excel—the changing of which cell is currently selected. This means that every time you move from one cell to another, the value in B4 is checked and column H is either hidden or unhidden.
If it is possible that the contents of cell B4 could be empty, then it is possible that Excel will interpret that emptiness as a zero value. In that case, you can modify the macro just a bit so that it checks for an empty cell.
Sub HideColumn2() Dim rCell As Range Set rCell = Range("B4") Columns("H").EntireColumn.Hidden = False If (Not IsEmpty(rCell)) And (IsNumeric(rCell) And (rCell.Value = 0) Then Columns("H").EntireColumn.Hidden = True End If End Sub
This version of the macro actually checks three conditions: that B4 is not empty, that it contains a numeric value, and that the value is 0. If all three of these conditions are met, then column H is hidden.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3283) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Hiding Columns Based on a Cell Value.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
There are times when displaying zero values in a worksheet (especially if there are lots of them) can be distracting from ...
Discover MoreHave you ever been using a workbook, only to open it one day and find that Excel has changed the height of your rows or ...
Discover MoreWhat are you to do if you are trying to format a worksheet, only to find out that one of the tools you need is not ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-08-19 05:38:26
Willy Vanhaelen
@Agnus White
You have to be more explicit with what you want. Must each cell in H4:HE4 have a zero value in it and then hide the whole range H4:HE4 or must each column in range H4:HE4 be tested and then hide each one with a zero value in row 4?
2022-08-18 06:00:16
Hi There
I have tried using the below to hide a column with a zero value in it. How can I extend this to check cells H4:HE4?
Any help would be greatly appreciated.
Thanks
Angus
Sub HideColumn1()
If Range("H4").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If
End Sub
2021-03-23 05:24:49
Hello Randy,
Looping a macro or looping set of code lines is a fairly basic code way of doing something along the lines of what you want.
In VBA there are a few ways to loop.
Possibly the simplest way to modify the last macro from Allen Wyatt is to use the
For / Next
type of looping
Example for deleting the entire row if cell value is 0 in the range
A12:A15
Sub HideColumn2()
Dim rCel As Range
For Each rCel In Range("A12:A15")
If (Not IsEmpty(rCel)) And (IsNumeric(rCel)) And (rCel.Value = 0) Then
rCel.EntireRow.Hidden = True
End If
Next rCel
End Sub
Alan Elston
2021-03-22 12:19:49
Randy
Thanks, this works very well. I am using the 2nd macro you provided above.
Is there a code to check the cell value of each cell in a column, and if it equals 0, hide that corresponding row? Or do I need an if/else statement for each row? I have ~100 rows to check.
2020-10-05 03:15:04
Hello David
The screen shot is not too clear so I am not 100% sure what you want. But generally something along the lines of what you want is very easy to do in VBA, but there will be very many different ways , the best way depending on exactly what you want to do
Explaining and exchanging coding and files is a bit restricted here in the comment section of Allen Wyatt’s Blog site.
Best is to ask for help at one of the excel help forums. You will usually get a lot of quick help at places like mrexcel or excelforum. ( If you are not in a rush, I can help with simple things like this when I have time at my forum , excelfox )
Alan Elston
2020-10-04 15:45:50
David Bayles
Hello,
I've created an Excel spreadsheet by exporting my contacts as a csv file. I'm trying to edit this massive list of often-irrelevant data. This could be helped by creating a formula or macro (?) to determine if a column has no data, except for the header, that column should be hidden from the table.
(see Figure 1 below)
I've searched the internet for a variety of solutions but none accomplish my wishes.
Any help you can provide is surely welcome. I can't be the only person wanting this resolved.
Thanx!
Figure 1.
2020-07-30 13:19:28
Muhammad Umar Fahim
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B1").Value = 1 Then
Columns("D:AA").EntireColumn.Hidden = True
Else
Columns("D:AA").EntireColumn.Hidden = False
If Range("B1").Value = 2 Then
Columns("F:AA").EntireColumn.Hidden = True
Else
Columns("F:AA").EntireColumn.Hidden = False
If Range("B1").Value = 3 Then
Columns("H:AA").EntireColumn.Hidden = True
Else
Columns("H:AA").EntireColumn.Hidden = False
If Range("B1").Value = 4 Then
Columns("J:AA").EntireColumn.Hidden = True
Else
Columns("J:AA").EntireColumn.Hidden = False
If Range("B1").Value = 5 Then
Columns("L:AA").EntireColumn.Hidden = True
Else
Columns("L:AA").EntireColumn.Hidden = False
If Range("B1").Value = 6 Then
Columns("N:AA").EntireColumn.Hidden = True
Else
Columns("N:AA").EntireColumn.Hidden = False
If Range("B1").Value = 7 Then
Columns("P:AA").EntireColumn.Hidden = True
Else
Columns("P:AA").EntireColumn.Hidden = False
If Range("B1").Value = 8 Then
Columns("R:AA").EntireColumn.Hidden = True
Else
Columns("R:AA").EntireColumn.Hidden = False
If Range("B1").Value = 9 Then
Columns("T:AA").EntireColumn.Hidden = True
Else
Columns("T:AA").EntireColumn.Hidden = False
If Range("B1").Value = 10 Then
Columns("V:AA").EntireColumn.Hidden = True
Else
Columns("V:AA").EntireColumn.Hidden = False
If Range("B1").Value = 11 Then
Columns("X:AA").EntireColumn.Hidden = True
Else
Columns("X:AA").EntireColumn.Hidden = False
If Range("B1").Value = 12 Then
Columns("Z:AA").EntireColumn.Hidden = True
Else
Columns("Z:AA").EntireColumn.Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
why it is not working?
2020-06-25 19:33:43
Danielle
Hi,
I would like to hide an entire column based on several totals being $0 in the rows that contain the word "total" in a particular column.
In other words, there are several totals at the bottom of my data set, and the labels for these totals are in a column to the left (but not the far left, so VLOOKUP would not work). Additionally, there is not a grand total at the bottom of each column, because different totals get totaled together. (I can't share image because it is sensitive data.) So, I have 8 rows that total particular values from each column. If all 8 of those rows show a $0 for any column, I just want the column to be hidden.
How would I do this easily? I have been manually scrolling down to check those 8 rows, and if they are all $0, I right click the column header and select 'hide'. I tried recording a macro with those steps, but I don't know how to tell the macro to validate that the 8 total rows are all $0. I can generally figure out VBA code by studying it, but I don't know enough to write it myself.
Any help is greatly appreciated!
2020-04-30 02:58:01
Edit.... I meant
...Allen Wyatt is using as example the value of 0 in range B4 as the criteria for hiding a COLUMN....
Alan Elston
2020-04-30 02:56:18
Hello Taha
Allen Wyatt has presented several macros here. They work slightly differently. But the main linking concept is hiding a column based on a single cell value.
Allen Wyatt is using as example the value of 0 in range B4 as the criteria for hiding a row
The idea of hiding a column based on a ranges value is a bit of a strange concept. You will necessarily have a range of values in a range.
So it is a bit difficult to understand what you are asking.
Alan Elston
2020-04-29 14:01:17
Taha
Hello Allen,
Thanks for sharing your knowledge. How can we apply the code to range say (A1:A50) instead of (B4)
2020-03-03 03:26:34
I came across another version of Willy's Toggle idea
Sub Toggle() ' e i l e e n s l o u n g e . c o m /viewtopic.php?f=27&t=34138#p264721
Columns("A:B").Hidden = Not Columns("A:B").Hidden
' Columns("A:B").Hidden = Columns("A:B").Hidden = False
End Sub
2020-01-16 02:59:48
Bas
Love this post and the comments, it solved some of my tasks. Thanks!
2019-03-09 03:16:08
Hi Willy, I tried to remember that neat little “Toggle” code line. I remembered seeing something similar here from you before , but forgot.
At first glance it might look to some people like a typo here because it may get broken up depending on what zoom you have, but for anyone thinking that … it is actually a single code line.., pseudo like
Clm.EntClm.Hdn= Clm.EntClm.Hdn=False
If you think about it, it is a simple neat logic idea,
VBA works backwards so evaluates first
Clm.EntClm.Hdn=False
That will return either False or True, which will then be applied to the first Clm.EntClm.Hdn
If you follow the logic through you can see that it supplies Clm.EntClm.Hdn with the opposite to what it has
Some of the simplest ideas are the best.
(see Figure 1 below)
Figure 1.
2019-03-08 11:33:56
Willy Vanhaelen
@ Alan
Your solution will only work if on clicking cell P1 the cellpointer is elsewhere but is has the advantage that is also works if you move the cellpointer to cell P1 using the arrow keys.
Here is a shorter version that works equally well:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$P$1" Then Exit Sub
Columns("Q:T").EntireColumn.Hidden = Columns("Q:T").EntireColumn.Hidden = False
End Sub
@Claudia
You can also use the BeforeRightClick or BeforeDoubbleClick events. Then you will hide/unhide the columns by right/double clicking cell P1 even if the cell pointer is already there.
This macro is for right click:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "$P$1" Then Exit Sub
Columns("Q:T").EntireColumn.Hidden = Columns("Q:T").EntireColumn.Hidden = False
Cancel = True
End Sub
If you prefer double click replace Right with Double in the first line of the macro.
Remember: an event macro like these must be placed in the code page of the sheet you want to use it in. Right click it's tab en select "View Code..." That's the place to be.
2019-03-08 03:47:44
Hi Claudia.
Maybe this will help get you started:
When such a routine is started by Excel, it passes the range object of the selected cells into the variable Target
We can then look at that range object, Target, to give us information about the selected cells, for example their address.
Here is a slightly modified form of Allen Wyatt’s routine. It will hide and unhide column H when you select cell P1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$P$1" Then
If Columns("H").EntireColumn.Hidden = True Then
Columns("H").EntireColumn.Hidden = False
Else
Columns("H").EntireColumn.Hidden = True
End If
Else
End If
End Sub
Alan Elston
2019-03-07 07:37:39
Claudia
I am needing to automatically hide columns and un hide them by clicking on a column - is this possible? So for example --- I have heading in P1 which I want that to allows show on the spreadsheet, but when I want the "hide" columns to show I click on P1 and it will un hide column Q,R,S and T
2018-08-03 15:20:42
Jannette Ruiz
This might be a silly question but I don't know how to unhide cells. I was able to hide based on a "Yes" or "No" question. But how do I unhide previously hidden cells?
2018-02-26 13:18:18
Mark Breese
First, Thanks for this. Very helpful.
I do have one question. I've set this up to work with a data validation drop down list (In E14) selection to hide and unhide a row. However, when I select the term ("yes/no" in this instance) the row does not hide/unhide until you click out of the cell with the drop down. Is there a way to make the hide/unhide happen with out having to click out of the cell?
the code I am using is this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E14").Value = "Yes" Then
Rows("15").EntireRow.Hidden = False
Else
Rows("15").EntireRow.Hidden = True
End If
End Sub
2018-02-26 11:03:14
Mark Breese
First, Thanks for this. Very helpful.
I do have one question. I've set this up to work with a data validation drop down list (In E14) selection to hide and unhide a row. However, when I select the term ("yes/no" in this instance) the row does not hide/unhide until you click out of the cell with the drop down. Is there a way to make the hide/unhide happen with out having to click out of the cell?
the code I am using is this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E14").Value = "Yes" Then
Rows("15").EntireRow.Hidden = False
Else
Rows("15").EntireRow.Hidden = True
End If
End Sub
2017-08-29 13:10:36
Monte Tucker
I was looking for a way of doing the same Data Filter that temporarily hides Rows based upon Column values, but I wanted to hide the Columns based upon Row values instead.
Sure, I could write a macro that pops up a window asking for the cell to match (or enter a value), and then hide each matching column using the selected cell (or Match or some similar method), but I was hoping on avoiding a macro if possible.
2017-08-17 14:55:37
Willy Vanhaelen
@MrVanderBrink
With Worksheet_SelectionChange the macro runs each time the cell pointer is moved even when you don't change anything.
With Worksheet_Change the macro only runs when you change anything in a cell.
The latter is to be preferred in most cases.
2017-08-16 05:17:09
MrVanderBrink
Dear Allen,
Reply to self
change
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
into
Private Sub Worksheet_Change(ByVal Target As Range)
helps a lot in speed (dont know why)
Still I am curious if it also can be fixed with using coulmns as variables. How would the code look like?
2017-08-16 05:10:37
MrVanderBrink
Dear Allen,
What would be a proper way to use this for multiple columns?
My example below really slows down the sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("U").EntireColumn.Hidden = [U1] = 1
Columns("V").EntireColumn.Hidden = [V1] = 1
Columns("W").EntireColumn.Hidden = [W1] = 1
Columns("X").EntireColumn.Hidden = [X1] = 1
Columns("Y").EntireColumn.Hidden = [Y1] = 1
Columns("Z").EntireColumn.Hidden = [Z1] = 1
Columns("AA").EntireColumn.Hidden = [AA1] = 1
Columns("AB").EntireColumn.Hidden = [AB1] = 1
Columns("AC").EntireColumn.Hidden = [AC1] = 1
Columns("AD").EntireColumn.Hidden = [AD1] = 1
Columns("AE").EntireColumn.Hidden = [AE1] = 1
Columns("AF").EntireColumn.Hidden = [AF1] = 1
etc...
etc...
End sub
Thanks!
2017-07-18 18:10:57
Bec
I was able to hide columns based on the value in that column at a certain row (28 in this case).
This works as a 'horizontal filter'.
Columns("O").EntireColumn.Hidden = Range("O28").Value = ""
Columns("P").EntireColumn.Hidden = Range("P28").Value = ""
Columns("Q").EntireColumn.Hidden = Range("Q28").Value = ""
Columns("R").EntireColumn.Hidden = Range("R28").Value = ""
Columns("S").EntireColumn.Hidden = Range("S28").Value = ""
...etc for each column applicable. In my case, many columns!
However - I have many rows that I want to put this filter on.
I know I could repeat this macro for each row eg
Columns("O").EntireColumn.Hidden = Range("O29").Value = ""
Columns("P").EntireColumn.Hidden = Range("P29").Value = ""
but I don't want hundreds of macros assigned to separate buttons.
What I really need is one macro that is relative to the row it is triggered from - so I have been experimenting with Relative References.
Columns("O").EntireColumn.Hidden = ActiveCell.Offset(0, 1).Value = ""
Columns("P").EntireColumn.Hidden = ActiveCell.Offset(0, 2).Value = ""
Columns("Q").EntireColumn.Hidden = ActiveCell.Offset(0, 3).Value = ""
etc..
With the active cell being N28 (where I've put a button).
However, for the life of me I cannot make this work.
Can someone help or give feedback on what I'm doing wrong? I am new to relative references, it's obvious I'm not instructing excel correctly here.
Appreciate you time!
2017-05-11 11:43:24
Willy Vanhaelen
@Miriam
You can only have one event macro in a sheet so the first macro works and hides or unhides the columns depending on the value in D2.
Your second macro is not an event macro so it will only be executed if you explicitely run it.
I noticed that the two macro codes are identical except for the words Penguin and Elephant. So you can combine the two macros into one by replacing in the first macro:
If Range("D2").Value = "Penguin" Then
with:
If Range("D2").Value = "Penguin" Or Range("D2").Value = "Elephant" Then
2017-05-10 16:14:17
Miriam
Hi! I am successfully created one macro, however I have a few others that I need to create for the same sheet, and I am having difficulty getting it to work. Below are the two macros I wrote and information about the pages that they are on. I am not sure what I need to do in order to have the two macros both working. Thank you in advance!
The one that is working successfully is in a window called "File Name" - Sheet1 (Code) and what I wrote is:
Sub Penguin()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("D2").Value = "Penguin" Then
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Else
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
End If
End Sub
My question is how to add another macro. I have tried adding it to the same sheet1 window as well as putting it in a Module, but neither makes it work. Below is what I wrote for the second macro:
Sub Elephant()
If Range("D2").Value = "Elephant" Then
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Else
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
End If
End Sub
2017-03-09 07:26:45
Tom Philpott
Thanks for this! It's close to what I need to do but without the VBasic knowledge, I'm not able to adjust the code for my needs.
What I need is the following:
- A toggle) button which runs a macro which hides any row in which column A is empty
- At the same time, columns C-F should be hidden by the same macro (no matter what values are).
And if I click on the button, I'd like it to show all columns and all rows.
Is that possible?
THanks!
Tom
2017-03-02 12:52:53
GMM
Hey! I create a VBA code for hiding columns based on cell value of a specific range. I wanna to change specific range in dynamic range(vlookup)
Private Sub ComboBox1_Change()
End Sub
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("L32:BE32")
c.EntireColumn.Hidden = (c.Value = 0)
Next c
Application.ScreenUpdating = True
End Sub
I wanna to change specific range(''L32:BE32") with a vlookup formula who search specific word and modify in code. I mean, if I insert or delete some rows in sheet, I must to change manually VBA code to new (L99:BE99)(ex) to can code work.
I wanna to do that automatic(I think with vlookup search based on specific criteria) and change L32:BE32 in L99:BE99 were 32/99 is a row number in sheet.
Can you help me? Thx in advance!
P.S.: Sry for grammatical error, is not my language!
2017-02-17 18:39:15
scott
'For Chintan, enter this macro and run it. Maybe add a button and assign it this macro
Sub dates()
Dim i As Long
Dim lc As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column
Dim dstart As Date, dend As Date
dstart = InputBox("Enter a start date, mm/dd/yyyy")
dend = InputBox("Enter an end date, mm/dd/yyyy")
For i = 1 To lc
If Cells(2, i) > dstart And Cells(2, i) < dend Then
Cells(2, i).EntireColumn.Hidden = False
Else
Cells(2,i).EntireColumn.Hidden = True
End If
Next i
End Sub
2017-02-10 04:46:26
Chintan
Hi I am trying to find a solution for display columns only between two dates. As my model has 365 columns each corresponding to a day in a year. So want to hide colums for date input provided by me. Say 1st Feb to 15th Feb. Then it should automatically hid all colums excepts pertaining to above dates between two dates.
will sincerely appreciate your advice on this guys.
2016-12-16 16:37:47
Kush
Hi,
What if I have a file where I want raws to to be visible for data entry only if they meet a certain criteria. Eg, I have workers who keep on asking for casuals though we are well establised. We have a excel file where they fill the data but it open so they currently fill person. I have a formula that calculates staff on duty, on sick leave or on vacation and it gives the variance. So is it possible to unhide the cells based on variance number?
Kush
2016-12-14 12:00:36
Andy
Thanks Willy!!! It works! I had it in a normal VBA Developer section but didn't know about the sheet's code page.
Thanks again,
Andy
2016-12-14 10:43:49
Willy Vanhaelen
@Andy
Here is a one liner that does the job:
Private Sub Worksheet_Change(ByVal Target As Range)
Columns("H").EntireColumn.Hidden = [B4] = 0
End Sub
It is very important that you put this macro in the sheet's code page. Right click the sheet's tab and select "View Code". That's the place to be.
2016-12-13 17:48:25
Andy
Hi,
I tried using the original code for hiding columns in real time but it doesn't seems to work in my excel 2010 with .xlsm. Anything I'm doing wrong?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B4").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If
End Sub
2016-09-06 21:24:28
Tony Waugh
I wish to hide a column in a worksheet contained in a workbook with several worksheets when a cell contains the word "Choose..." and unhide when it contains other text.
2016-07-25 01:08:56
Aram
Hi, I would like some help on being able to hide rows based on data being available/not available in three columns at the same time. So I would like excel (VBA code) to check if I17, J17 and K17 (up to I300, J300 and K300) columns have any data in them or not... if not then hide the entire row. Whats the code for this in its entirety. I am new at VBA so I am unable to write it. Any help will be greatly appreciated.
2016-06-02 09:50:52
Paula
Is there a way to hide the cell if the return is FALSE from an assigned condition?
2016-05-20 19:15:16
Brian
Great post, quick question.
If I wanted cell B4 to hide column H when there was a text word instead of a value how would I do that? Example when B4 has the word "Apple" then column H would hide.
2016-05-16 21:29:44
Holly
The second piece of code works well for an excel problem I have. Is there anyway to make the code work automatically?
2016-04-20 22:18:49
Raul Bahena
Hi Allen,
Thank you for your commitment to this site and overall passion for sharing knowledge. I was able to modify the code as follows to auto hide/unhide column range based on value of specific cell address and is working beautifully!! Please see below and wish you continue success.
Raul.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B23").Value = 1 Then
Columns("C:T").EntireColumn.Hidden = True
Else
Columns("C:T").EntireColumn.Hidden = False
If Range("B23").Value = 2 Then
Columns("D:T").EntireColumn.Hidden = True
Else
Columns("D:T").EntireColumn.Hidden = False
If Range("B23").Value = 3 Then
Columns("E:T").EntireColumn.Hidden = True
Else
Columns("E:T").EntireColumn.Hidden = False
If Range("B23").Value = 4 Then
Columns("F:T").EntireColumn.Hidden = True
Else
Columns("F:T").EntireColumn.Hidden = False
If Range("B23").Value = 5 Then
Columns("G:T").EntireColumn.Hidden = True
Else
Columns("G:T").EntireColumn.Hidden = False
If Range("B23").Value = 6 Then
Columns("H:T").EntireColumn.Hidden = True
Else
Columns("H:T").EntireColumn.Hidden = False
If Range("B23").Value = 7 Then
Columns("I:T").EntireColumn.Hidden = True
Else
Columns("I:T").EntireColumn.Hidden = False
If Range("B23").Value = 8 Then
Columns("J:T").EntireColumn.Hidden = True
Else
Columns("J:T").EntireColumn.Hidden = False
If Range("B23").Value = 9 Then
Columns("K:T").EntireColumn.Hidden = True
Else
Columns("K:T").EntireColumn.Hidden = False
If Range("B23").Value = 10 Then
Columns("L:T").EntireColumn.Hidden = True
Else
Columns("L:T").EntireColumn.Hidden = False
If Range("B23").Value = 11 Then
Columns("M:T").EntireColumn.Hidden = True
Else
Columns("M:T").EntireColumn.Hidden = False
If Range("B23").Value = 12 Then
Columns("N:T").EntireColumn.Hidden = True
Else
Columns("N:T").EntireColumn.Hidden = False
If Range("B23").Value = 13 Then
Columns("O:T").EntireColumn.Hidden = True
Else
Columns("O:T").EntireColumn.Hidden = False
If Range("B23").Value = 14 Then
Columns("P:T").EntireColumn.Hidden = True
Else
Columns("P:T").EntireColumn.Hidden = False
If Range("B23").Value = 15 Then
Columns("Q:T").EntireColumn.Hidden = True
Else
Columns("Q:T").EntireColumn.Hidden = False
If Range("B23").Value = 16 Then
Columns("R:T").EntireColumn.Hidden = True
Else
Columns("R:T").EntireColumn.Hidden = False
If Range("B23").Value = 17 Then
Columns("S:T").EntireColumn.Hidden = True
Else
Columns("S:T").EntireColumn.Hidden = False
If Range("B23").Value = 18 Then
Columns("T:T").EntireColumn.Hidden = True
Else
Columns("T:T").EntireColumn.Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
2016-03-08 09:49:51
Willy Vanhaelen
@Utkarsh Singh
Change
Worksheet_SelectionChange(ByVal Target As Range)
in
Worksheet_Change(ByVal Target As Range)
But you can make it even a one liner:
Private Sub Worksheet_Change(ByVal Target As Range)
Columns("H").EntireColumn.Hidden = Range("B4") = 0
End Sub
2016-03-08 04:25:15
Utkarsh Singh
The macro works great but suppose if the cell value is a list (like Data Validation list" then if we select one option which is zero then it does not refresh immediately. One has to deselect that cell and move to other cell for result to be shown.
Does anybody know how to fix this?
2016-02-07 02:58:57
Mark Cramoysan
Of course if you just want the data to disappear then a Conditional Format can be the answer - I use white writing on a white background for all sorts of things where the data needs to not be immediately visible but so it can be seen if needed.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments