Macro to hide and unhide

[stathisaska]

Hello excel community.

I have a problem, the solution of which will save me hours.

I need something to unhide AX:FX if GX is "CUSTOM"
and hide again AX:FX if HX is "OK"


Can you please help ?

Thanks

 

[Joe4]

Welcome to the board!
Which row are you looking at?
You can only hide entire columns for all rows.

 

[Joe4]

If looking at Row 1, then the commands would look something line:

    If Range("GX1") = "CUSTOM" Then Columns("AX:FX").EntireColumn.Hidden = False
    If Range("HX1") = "OK" Then Columns("AX:FX").EntireColumn.Hidden = True

Note that if both conditions are met, the last command run will win.

 

[Rick Rothstein]

If looking at Row 1, then the commands would look something line:

    If Range("GX1") = "CUSTOM" Then [COLOR=#FF0000][B]Columns("AX:FX").EntireColumn[/B][/COLOR].Hidden = False
    If Range("HX1") = "OK" Then [COLOR=#FF0000][B]Columns("AX:FX").EntireColumn[/B][/COLOR].Hidden = True

Note that if both conditions are met, the last command run will win.

Why not just Columns("A:F") in place of what I highlighted in red?

 

[Joe4]

Why not just Columns("A:F") in place of what I highlighted in red?

I think you mean: Columns("AX:FX"), don't you?


You are right, the "EntireColumn" is not needed (remnant of Macro Recorder), but columns AX:FX are not the same as columns A:F!


EDIT: I see that X could interpretted multiple ways, as a column (AX is a valid column reference), or as a variable they were using to denote rows. Later posts would seem to suggest the later.

 

[stathisaska]

Thank you all for your replies !
Ok, let me put that into perspective.

Think columns A:F.
They are hidden.
I am working the rows, values in A:F are automatically calculated.
Sometimes I need to manully change the values of A:F columnts at that specific row and hide them again

I need an automated procedure because there many sets of columns I need to hide/unhide besides A:F

I think the commands you provided will do the job, but where do I enter them ?

Thank you again for your valuable help !

 

[bruderbell]

It seems like what you need is some conditional formulas for A:F that trigger on a reference cell. You may need to provide insight into those formulas to get an answer.

You cannot hide cells, only entire columns.

 

[Joe4]

As mentioned, you can only hide an entire row, or a entire column. You cannot hide column "A" for Row 1, but not for Row 2.

So what exactly are you looking for?
Walking us through an actual example may help.

 

[stathisaska]

Yes, I want to hide the entire column
I said AX:FX, because in my head, using only the if function of excel, it is something like this


A1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN A; UNHIDE COLUMN A); 5)
B1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN B; UNHIDE COLUMN B); 5)
C1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN C; UNHIDE COLUMN C); 5)
D1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN D; UNHIDE COLUMN D); 5)
E1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN E; UNHIDE COLUMN E); 5)
F1 cell =IF(G1="CUSTOM"; IF(H1="OK"; HIDE COLUMN F; UNHIDE COLUMN F); 5)

A2 cell =IF(G2="CUSTOM"; IF(H2="OK"; HIDE COLUMN A; UNHIDE COLUMN A); 5)
etc ... (5 would be an example of the value if nothing happens...)

Meaning, that while working on my spredsheet,

-A:F are hidden
-I type in G5 cell "CUSTOM"
-triggers columns A:F to unhide
-I make my custom changes in cells A5:F5
-I type in H5 "OK"
-A:F columns hide again