Hide Column(s) Based On Certain Criteria Using Macro in Excel 2007 « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » VBA & Macros

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

(9 posts) (2 voices)

Started 1 year ago by 4rcher91
Latest reply from 4rcher91

Tags:

4rcher91

Member
Joined: Mar '12
Posts: 10

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.

Posted 1 year ago #
kchiba

Member
Joined: May '10
Posts: 260

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.

Posted 1 year ago #
4rcher91

Member
Joined: Mar '12
Posts: 10

Thank you. I will give it a try.

Posted 1 year ago #
4rcher91

Member
Joined: Mar '12
Posts: 10

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

Posted 1 year ago #
kchiba

Member
Joined: May '10
Posts: 260

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

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

Posted 1 year ago #
4rcher91

Member
Joined: Mar '12
Posts: 10

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?

Posted 1 year ago #
4rcher91

Member
Joined: Mar '12
Posts: 10

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.

Posted 1 year ago #
kchiba

Member
Joined: May '10
Posts: 260

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.

Posted 1 year ago #
4rcher91

Member
Joined: Mar '12
Posts: 10

Thank you for your time and response :)

Posted 1 year ago #