PDA

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!!!