PDA

View Full Version : macro Code Short Cut to Hide Column



gengcas
11-26-2010, 12:24 AM
I have 2 question:
1. Is there a way to shorten the code below?

2. How will I insert a command that when you change the value of B2, the macro will automatically run(without me clicking run after I enter a value in B2)?

Public Sub Test()
If Range("B2").Value = Range("C5") Then
Range("C5").EntireColumn.Hidden = False
Else
Range("C5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("D5") Then
Range("D5").EntireColumn.Hidden = False
Else
Range("D5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("E5") Then
Range("E5").EntireColumn.Hidden = False
Else
Range("E5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("F5") Then
Range("F5").EntireColumn.Hidden = False
Else
Range("F5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("G5") Then
Range("G5").EntireColumn.Hidden = False
Else
Range("G5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("H5") Then
Range("H5").EntireColumn.Hidden = False
Else
Range("H5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("I5") Then
Range("I5").EntireColumn.Hidden = False
Else
Range("I5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("J5") Then
Range("J5").EntireColumn.Hidden = False
Else
Range("J5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("K5") Then
Range("K5").EntireColumn.Hidden = False
Else
Range("K5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("L5") Then
Range("L5").EntireColumn.Hidden = False
Else
Range("L5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("M5") Then
Range("M5").EntireColumn.Hidden = False
Else
Range("M5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("N5") Then
Range("N5").EntireColumn.Hidden = False
Else
Range("N5").EntireColumn.Hidden = True
End If

If Range("B2").Value = Range("O5") Then
Range("O5").EntireColumn.Hidden = False
Else
Range("O5").EntireColumn.Hidden = True
End If
If Range("B2").Value = Range("P5") Then
Range("P5").EntireColumn.Hidden = False
Else
Range("P5").EntireColumn.Hidden = True
End If
End Sub


Thanks!!!

GTO
11-26-2010, 01:25 AM
Greetings,


Disclaimer: Way too much turkey today and not well tested, but I believe this works.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("B2"), Target) Is Nothing _
And Target.Count = 1 Then

Range("C5").EntireColumn.Hidden = Not Range("B2").Value = Range("C5").Value
Range("D5").EntireColumn.Hidden = Not Range("B2").Value = Range("D5").Value
Range("E5").EntireColumn.Hidden = Not Range("B2").Value = Range("E5").Value
Range("F5").EntireColumn.Hidden = Not Range("B2").Value = Range("F5").Value
Range("G5").EntireColumn.Hidden = Not Range("B2").Value = Range("G5").Value
Range("H5").EntireColumn.Hidden = Not Range("B2").Value = Range("H5").Value
Range("I5").EntireColumn.Hidden = Not Range("B2").Value = Range("I5").Value
Range("J5").EntireColumn.Hidden = Not Range("B2").Value = Range("J5").Value
Range("K5").EntireColumn.Hidden = Not Range("B2").Value = Range("K5").Value
Range("L5").EntireColumn.Hidden = Not Range("B2").Value = Range("L5").Value
Range("M5").EntireColumn.Hidden = Not Range("B2").Value = Range("M5").Value
Range("N5").EntireColumn.Hidden = Not Range("B2").Value = Range("N5").Value
Range("O5").EntireColumn.Hidden = Not Range("B2").Value = Range("O5").Value
Range("P5").EntireColumn.Hidden = Not Range("B2").Value = Range("P5").Value
End If
End Sub



Please Note: This goes in the appropriate worksheet's module, as it uses a worksheet event. Right-click the sheet's tab, and select View Code.


Hope that helps,


Mark

mohanvijay
11-26-2010, 02:00 AM
try this


Private Sub Worksheet_Change(ByVal Target As Range)

Dim colcount, i As Integer

If Target.Address = "$B$2" Then
Cells.EntireColumn.Hidden = False
colcount = Cells(5, Columns.Count).End(xlToLeft).Column
For i = 3 To colcount
If Trim(UCase(Cells(5, i).Value)) <> Trim(UCase(Target.Value)) Then
Cells(5, i).EntireColumn.Hidden = True
End If
Next i
End If

End Sub

Bob Phillips
11-26-2010, 03:23 AM
Greetings,


Disclaimer: Way too much turkey today and not well tested, but I believe this works.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("B2"), Target) Is Nothing _
And Target.Count = 1 Then

Range("C5").EntireColumn.Hidden = Not Range("B2").Value = Range("C5").Value
Range("D5").EntireColumn.Hidden = Not Range("B2").Value = Range("D5").Value
Range("E5").EntireColumn.Hidden = Not Range("B2").Value = Range("E5").Value
Range("F5").EntireColumn.Hidden = Not Range("B2").Value = Range("F5").Value
Range("G5").EntireColumn.Hidden = Not Range("B2").Value = Range("G5").Value
Range("H5").EntireColumn.Hidden = Not Range("B2").Value = Range("H5").Value
Range("I5").EntireColumn.Hidden = Not Range("B2").Value = Range("I5").Value
Range("J5").EntireColumn.Hidden = Not Range("B2").Value = Range("J5").Value
Range("K5").EntireColumn.Hidden = Not Range("B2").Value = Range("K5").Value
Range("L5").EntireColumn.Hidden = Not Range("B2").Value = Range("L5").Value
Range("M5").EntireColumn.Hidden = Not Range("B2").Value = Range("M5").Value
Range("N5").EntireColumn.Hidden = Not Range("B2").Value = Range("N5").Value
Range("O5").EntireColumn.Hidden = Not Range("B2").Value = Range("O5").Value
Range("P5").EntireColumn.Hidden = Not Range("B2").Value = Range("P5").Value
End If
End Sub



Please Note: This goes in the appropriate worksheet's module, as it uses a worksheet event. Right-click the sheet's tab, and select View Code.


Hope that helps,


Mark


Why not just use



Columns("C").Hidden = Not Range("B2").Value = Range("C5").Value
'etc.

gengcas
11-29-2010, 06:20 PM
Thanks XLD!!!

Sean.DiSanti
11-29-2010, 06:54 PM
or you could use my answer from your other thread, in your SelectionChange event...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
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
***edit***
or even combine the 2 because i like the way xld's looks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For x = 99 To 114
Columns(Chr(x)).Hidden = Not Range("B2").Value = Range(Chr(x) & "5").Value Next
End Sub

GTO
12-01-2010, 12:26 AM
Why not just use



Columns("C").Hidden = Not Range("B2").Value = Range("C5").Value
'etc.


:blush Oops, yes sir :bow: