| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Writing a macro to hide columns based on cell value
I'm trying to write a macro that will automatically hide certain columns
based on a cell's value. Basically, if C3 = "Sales" I need columns R, S, and T to be hiden; however if C3 does not equal "Sales" i need columns L, M and N to be hiden, but R, S, and T need to come back into view and vice versa. Is there a macro that can assist me? Please help. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Writing a macro to hide columns based on cell value
Right click on sheet tab, view code, and paste appropriate macro in.
If C3 is controlled by formula: Private Sub Worksheet_Calculate() If Range("c3").Value = "Sales" Then Columns("R:T").EntireColumn.Hidden = True Columns("L:N").EntireColumn.Hidden = False Else Columns("R:T").EntireColumn.Hidden = False Columns("L:N").EntireColumn.Hidden = True End If End Sub If inputted manually: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("C3") Then If Range("c3").Value = "Sales" Then Columns("R:T").EntireColumn.Hidden = True Columns("L:N").EntireColumn.Hidden = False Else Columns("R:T").EntireColumn.Hidden = False Columns("L:N").EntireColumn.Hidden = True End If End If End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JAbels001" wrote: I'm trying to write a macro that will automatically hide certain columns based on a cell's value. Basically, if C3 = "Sales" I need columns R, S, and T to be hiden; however if C3 does not equal "Sales" i need columns L, M and N to be hiden, but R, S, and T need to come back into view and vice versa. Is there a macro that can assist me? Please help. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Writing a macro to hide columns based on cell value
Worked like a charm! Thanks!
"Luke M" wrote: Right click on sheet tab, view code, and paste appropriate macro in. If C3 is controlled by formula: Private Sub Worksheet_Calculate() If Range("c3").Value = "Sales" Then Columns("R:T").EntireColumn.Hidden = True Columns("L:N").EntireColumn.Hidden = False Else Columns("R:T").EntireColumn.Hidden = False Columns("L:N").EntireColumn.Hidden = True End If End Sub If inputted manually: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("C3") Then If Range("c3").Value = "Sales" Then Columns("R:T").EntireColumn.Hidden = True Columns("L:N").EntireColumn.Hidden = False Else Columns("R:T").EntireColumn.Hidden = False Columns("L:N").EntireColumn.Hidden = True End If End If End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JAbels001" wrote: I'm trying to write a macro that will automatically hide certain columns based on a cell's value. Basically, if C3 = "Sales" I need columns R, S, and T to be hiden; however if C3 does not equal "Sales" i need columns L, M and N to be hiden, but R, S, and T need to come back into view and vice versa. Is there a macro that can assist me? Please help. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic Hide group of a columns based on cel value | Excel Discussion (Misc queries) | |||
| Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
| Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
| Formula or Macro to hide a row based on a cell value | Excel Discussion (Misc queries) | |||
| How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Linear Mode
