Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide columns based on related cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide columns based on related cell
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 '<<============ --- Regards. Norman "Lisa" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide columns based on related cell
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. -- Regards, OssieMac "Lisa" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide columns based on related cell
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 "OssieMac" wrote: 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. -- Regards, OssieMac "Lisa" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide columns based on related cell
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 '<<============ --- Regards. Norman "Norman Jones" wrote in message ... 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 '<<============ --- Regards. Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Columns based on a cell value | Excel Worksheet Functions | |||
Writing a macro to hide columns based on cell value | Excel Discussion (Misc queries) | |||
Checkbox to Hide/Unhide Columns Based on Value in Cell | Excel Programming | |||
Hide or Unhide certain columns based on a cell value | Excel Programming | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions |