Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and unhide columns
I have the following statement. Instead of hiding and unhiding the range
"B:J", I want to hide and unhide Columns "B,D,f,H" Please find below the statement I have used. Any advise would be very much appreciated. Private Sub CommandButton1_Click() Macro2 End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim arr As Variant arr = Array("EO", "EC", "ES", "EV", "EF", "EG") If Target.Cells.Count 1 Then Exit Sub If Target.Column = 1 Then Columns("B:J").EntireColumn.Hidden = _ IsError(Application.Match(Target.Value, arr, 0)) End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Union(Range("A:A"), Range("B:J"))) Is Nothing Then Columns("B:J").EntireColumn.Hidden = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and unhide columns
Columns("B:B,D:D,F:F,H:H").EntireColumn.Hidden.... ...etc
(BTW: The macro recorder could have told you the same) Wolf "Frederic" wrote: I have the following statement. Instead of hiding and unhiding the range "B:J", I want to hide and unhide Columns "B,D,f,H" Please find below the statement I have used. Any advise would be very much appreciated. Private Sub CommandButton1_Click() Macro2 End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim arr As Variant arr = Array("EO", "EC", "ES", "EV", "EF", "EG") If Target.Cells.Count 1 Then Exit Sub If Target.Column = 1 Then Columns("B:J").EntireColumn.Hidden = _ IsError(Application.Match(Target.Value, arr, 0)) End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Union(Range("A:A"), Range("B:J"))) Is Nothing Then Columns("B:J").EntireColumn.Hidden = True End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and unhide columns
Hi Frederick,
Try: Private Sub Worksheet_Change(ByVal Target As Range) Dim arr As Variant Dim rng As Range Set rng = Range("B1, D1, F1, H1") arr = Array("EO", "EC", "ES", "EV", "EF", "EG") If Target.Cells.Count 1 Then Exit Sub If Target.Column = 1 Then rng.EntireColumn.Hidden = _ IsError(Application.Match(Target.Value, arr, 0)) End If End Sub --- Regards, Norman "Frederic" wrote in message ... I have the following statement. Instead of hiding and unhiding the range "B:J", I want to hide and unhide Columns "B,D,f,H" Please find below the statement I have used. Any advise would be very much appreciated. Private Sub CommandButton1_Click() Macro2 End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim arr As Variant arr = Array("EO", "EC", "ES", "EV", "EF", "EG") If Target.Cells.Count 1 Then Exit Sub If Target.Column = 1 Then Columns("B:J").EntireColumn.Hidden = _ IsError(Application.Match(Target.Value, arr, 0)) End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Union(Range("A:A"), Range("B:J"))) Is Nothing Then Columns("B:J").EntireColumn.Hidden = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Unhide & Hide Columns with VBA | Excel Discussion (Misc queries) | |||
Automatically hide/unhide columns | Excel Discussion (Misc queries) | |||
Hide/Unhide columns using button on top over relevant columns | Excel Discussion (Misc queries) | |||
Unhide and hide columns | Excel Worksheet Functions | |||
hide / unhide columns | Excel Programming |