Drop down bar to hide column according to the content of a cell
 

Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z

Thank you so much.

Cheers
WL

Drop down bar to hide column according to the content of a cell
 

WL
This macro should do what you want. I wrote the macro so that you could
add conditions and ranges to hide for those conditions. Also, as written,
this macro will hide the designated columns in the sheet that holds the
drop-down and the drop-down is in cell A2. Note that this macro is a sheet
macro and must be placed in the sheet module of that sheet that holds the
drop-down. To access that module, right-click on the sheet tab, select View
Code, and paste this macro into that module. "X" out of the module to
return to the worksheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToHide As Range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case 2: Set RngToHide = Columns("G:Z")
Case 5: Set RngToHide = Columns("J:Z")
Case Else: Set RngToHide = Range("A1")
End Select
If RngToHide.Address(0, 0) = "A1" Then Exit Sub
Cells.EntireColumn.Hidden = False
RngToHide.EntireColumn.Hidden = True
End If
End Sub
wrote in message
ups.com...
Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z

Thank you so much.

Cheers
WL

Drop down bar to hide column according to the content of a cell
 

I'm sorry to bother you again..

If the columns to be hidden in another sheet, how shd i go about it??
E.g. The Drop Down is in Sheet "ABC", the columns to be hidden is in
Sheet "Final"

Otto Moehrbach wrote:
WL
This macro should do what you want. I wrote the macro so that you could
add conditions and ranges to hide for those conditions. Also, as written,
this macro will hide the designated columns in the sheet that holds the
drop-down and the drop-down is in cell A2. Note that this macro is a sheet
macro and must be placed in the sheet module of that sheet that holds the
drop-down. To access that module, right-click on the sheet tab, select View
Code, and paste this macro into that module. "X" out of the module to
return to the worksheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToHide As Range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case 2: Set RngToHide = Columns("G:Z")
Case 5: Set RngToHide = Columns("J:Z")
Case Else: Set RngToHide = Range("A1")
End Select
If RngToHide.Address(0, 0) = "A1" Then Exit Sub
Cells.EntireColumn.Hidden = False
RngToHide.EntireColumn.Hidden = True
End If
End Sub
wrote in message
ups.com...
Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z

Thank you so much.

Cheers
WL

Drop down bar to hide column according to the content of a cell
 

Daphie
Use this macro. Note that the sheet name ABC (the sheet that has the
drop-down cell) does not appear in the macro. You can call it anything you
want.
But the sheet name "Final" does appear in the macro, so if you want to
change that sheet name, you need to change the "Final" in the macro as well.
Note the leading periods in the 2 lines between "With Sheets("Final")" and
"End With". Make sure they are there. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToHide As Range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case 2: Set RngToHide = Columns("G:Z")
Case 5: Set RngToHide = Columns("J:Z")
Case Else: Set RngToHide = Range("A1")
End Select
If RngToHide.Address(0, 0) = "A1" Then Exit Sub
With Sheets("Final")
.Cells.EntireColumn.Hidden = False
.Range(RngToHide.Address).EntireColumn.Hidden = True
End With
End If
End Sub
"Daphie" wrote in message
oups.com...
I'm sorry to bother you again..

If the columns to be hidden in another sheet, how shd i go about it??
E.g. The Drop Down is in Sheet "ABC", the columns to be hidden is in
Sheet "Final"

Otto Moehrbach wrote:
WL
This macro should do what you want. I wrote the macro so that you
could
add conditions and ranges to hide for those conditions. Also, as
written,
this macro will hide the designated columns in the sheet that holds the
drop-down and the drop-down is in cell A2. Note that this macro is a
sheet
macro and must be placed in the sheet module of that sheet that holds the
drop-down. To access that module, right-click on the sheet tab, select
View
Code, and paste this macro into that module. "X" out of the module to
return to the worksheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToHide As Range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Target.Value
Case 2: Set RngToHide = Columns("G:Z")
Case 5: Set RngToHide = Columns("J:Z")
Case Else: Set RngToHide = Range("A1")
End Select
If RngToHide.Address(0, 0) = "A1" Then Exit Sub
Cells.EntireColumn.Hidden = False
RngToHide.EntireColumn.Hidden = True
End If
End Sub
wrote in message
ups.com...
Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z

Thank you so much.

Cheers
WL