View Full Version : Solved: Macro to Hide Columns Based on Options?
gengcas
11-25-2010, 11:20 PM
I have a dropdown option in Column B2.
I need a macro that will hide columns C5 to R5 if the value of the cells on rows C5 to R5 is not equal to column B2.
Please help...
Thanks a million!!!
I attached a sample output and problem description.
Thanks again!
Bob Phillips
11-26-2010, 03:21 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastcol As Long
Dim i As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
Lastcol = Me.Cells(5, Me.Columns.Count).End(xlToLeft).Column
For i = 3 To Lastcol
Me.Columns(i).Hidden = Me.Cells(5, i).Value <> Target.Value
Next i
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Bob Phillips
11-26-2010, 03:39 AM
That code might leave columns hidden. Try this instead
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastcol As Long
Dim i As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
Lastcol = Me.Cells(5, Me.Columns.Count).End(xlToLeft).Column
i = 3
Do While Me.Cells(5, i).Value <> ""
Me.Columns(i).Hidden = Not (Me.Cells(5, i).Value = Target.Value)
i = i + 1
Loop
End If
ws_exit:
Application.EnableEvents = True
End Sub
Sean.DiSanti
11-29-2010, 05:52 PM
another way to skin the same cat...
Public Sub hideem()
For x = 99 To 114
If ActiveSheet.Range(Chr(x) & "5").Formula <> ActiveSheet.Range("b5").Formula Then
ActiveSheet.Columns(Chr(x) & ":" & Chr(x)).Hidden = True
End If
Next
End Sub
gengcas
11-29-2010, 05:59 PM
Thanks XLD!!! for the nth time you're such a big help!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.