Hide columns based on related cell

[Lisa]

Hi
I have a spreadsheet used to order products from overseas. It takes three
columns to order one complete product. The supplier requires each product to
be ordered separately. This requires quite a few columns set up to take the
information.

Based on whether the product order is empty (based on product number saved
in cells (b5, e5, h5, k5, n5, q5, t5,w5, z5), I would like to hide the three
columns pertaining to that empty product order.


I tried to create a VBA code, which works to hide one set of 3 columns if
the related cell is empty, but if I have more than one, it hides all the
columns regardless of whether there is information in the related cells or
not. Can anyone help me to get this to work? Below is a example of where it
works for one. How do I modify it to take into account multiple ranges?

Private Sub HideEmptyCases_Click()
If Z5 = "" Then Columns("Z:AB").Hidden = True
(If I put anymore any more additional statements in here it hides more
regardless of whether the statement is true or false) e.g
If W5 = "" Then Columns("W:Y").Hidden = True

End Sub

I would really appreciate any suggestions or help.

Thanks
Lisa

 

[Norman Jones]

Hi Lisa,

Try:

'============>>
Option Explicit

Private Sub HideEmptyCases_Click()
With Me
.Columns.Hidden = False
.Columns("Z:AB").Hidden = IsEmpty(.Range("Z5"))
.Columns("W:Y").Hidden = IsEmpty(.Range("W5"))
End With
End Sub
'<<============

 

[OssieMac]

Hi Lisa,

the reason it is failing is because the syntax should be:-

If Range("Z5") = "" Then Columns("Z:AB").Hidden = True

Without the Range, VBA thinks that Z5 is a variable and it is null because
you have never assigned anything to it.

 

[Lisa]

Wonderful Thank you so much OssieMac! I knew it was going to be something
simple. It's working perfect now! Thanks also for a quick response. This
was my first time using the forum. Im sure I'll be back.

Cheers
Lisa

 

[Norman Jones]

Hi Lisa,

If, initially, you unhide all colums manually,
you could simplify my macro to read:

'============>>
Option Explicit

Private Sub HideEmptyCases_Click()
With Me
.Columns("Z:AB").Hidden = IsEmpty(.Range("Z5"))
.Columns("W:Y").Hidden = IsEmpty(.Range("W5"))
End With
End Sub
'<<============

As an alternative to the initial, manual unhide
operation, you could automate this with the
following simple macro:

'============>>
Public Sub UnhideAllColumns()
ActiveSheet.Columns.Hidden = False
End Sub
'<<============