Hiding Columns Based on a Cell Value

@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?

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

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

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.

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

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!

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?

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!

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

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

Hello Allen,
Thanks for sharing your knowledge. How can we apply the code to range say (A1:A50) instead of (B4)

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

Love this post and the comments, it solved some of my tasks. Thanks!

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)

@ 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.

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

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

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?

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

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

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.

@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.

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?

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!

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!

@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

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

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

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!

'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

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.

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

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

@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.

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

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.

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.

Is there a way to hide the cell if the return is FALSE from an assigned condition?

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.

The second piece of code works well for an excel problem I have. Is there anyway to make the code work automatically?

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

@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

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?

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.