Tech Support Guy banner
Status
Not open for further replies.

Solved: Hide Columns based on value of cell

13K views 7 replies 3 participants last post by  slurpee55 
#1 ·
Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C
if D24 is 0, then hide column D
etc...


Sub Hide_Column ()

If Range("H24").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If

End Sub
 
#4 ·
Sub M2()
Range("C:Z").EntireColumn.Hidden = False
Rows(1).Insert
Range("C1:Z1").FormulaR1C1 = "=IF(AND(R[24]C=0,R[24]C<>""""),#N/A)"
Range("C1:Z1").SpecialCells(xlCellTypeFormulas, 16).EntireColumn.Hidden = True
Rows(1).Delete
End Sub
 
#5 ·
bomb, old man, I know you are the far better coder, so what are the advantages of your code over mine (which I grabbed from somewhere else, but I don't know who wrote it or I would give credit for the work)??
Does it get around the problem with null cells?
 
#6 ·
Yep, I posted to address the null cells issue*. Run:

Range("C1:Z1").FormulaR1C1 = "=IF(AND(R[24]C=0,R[24]C<>""""),#N/A)"

by itself to get a formula with a "double-check" -- equals 0 and doesn't = "".

(*plus there's no loop :))
 
#8 ·
Yep, I posted to address the null cells issue*. Run:

Range("C1:Z1").FormulaR1C1 = "=IF(AND(R[24]C=0,R[24]C<>""""),#N/A)"

by itself to get a formula with a "double-check" -- equals 0 and doesn't = "".

(*plus there's no loop :))
Duh - I should have read the IF() more carefully. And no loop - much better!! :up: :up:
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top