Hide Column(s) Based On Certain Criteria Using Macro in Excel 2007

[4rcher91]

Hi,


I'm new to this forum. Any idea about macro that can hide a few columns, based on certain criteria on those columns?


I have already posted similar question at Ozgrid forum:

http://www.ozgrid.com/forum/showthread.php?t=163079&p=598550#post598550


Many thanks.

 

[kchiba]

Sub HideCol()


If Range("C5") = "Hide" Then

Columns("C:C").Select

Selection.EntireColumn.Hidden = True

End If


End Sub

This will hide Column C if C5 is Hide. Note that this macro must be run, it is not event driven.

 

[4rcher91]

Thank you. I will give it a try.

 

[4rcher91]

kchiba, what does the 3rd line from the top mean?

 

[kchiba]

Do you mean Columns ("C:C").select.


That is the column that is going to be hidden and the macro select it.

 

[4rcher91]

kchiba, your macro code works perfectly. I notice, it works only when you enter "Hide" on a single specific cell. How about if you enter on any cell on that particular column?

 

[4rcher91]

sorry for my last post, I managed to solve that. Thanks. The code is, just as an example:


'Range("P1").Range("P6")'


Please can you teach me how to hide a column, if the sum of all cells in that column equal to value 0? Thanks in advance.

 

[kchiba]

Hi,


You can use a helper cell in which you will total the respective column and if that cell is zero, you can hide it with the macro, by changing the If statement.

 

[4rcher91]

Thank you for your time and response