VBA code to unhide Unhide multiple columns based on multiple values in row

[Murali_K]

Hi Guys,

I have data in "C:F" columns, which is hidden. when i copy "C4P" in cell B6, it should unhide only column C. If i copy "C4P,C2P,C4S,CS" in "B6:B137" cells it should unhide "C", "D", "E" and "F" columns.
i got below code but it works for only one value in "B6:B137" and unhide one column.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range


Set Changed = Range("B6:B137")


If Not Intersect(Target, Changed) Is Nothing Then
Range("C:N").EntireColumn.Hidden = True
Select Case UCase(Target.Value)
Case "C2P"
Range("C:C").EntireColumn.Hidden = False
Case "CS"
Range("D: D").EntireColumn.Hidden = False
Case "C4P"
Range("E:E").EntireColumn.Hidden = False
Case "C4S"
Range("F:F").EntireColumn.Hidden = False

End Select


End If


Set Changed = Nothing


End Sub

Can someone help me to edit this code to achieve the above mentioned results.
please find attachment for your reference.
this is my first post, if any mistakes in this post, sorry for that.

Thanks in advance.

Murali K

[MagPower]

Remove the End Select statement and paste the following code in:


If Not Intersect(Target, Changed) Is Nothing Then
Range("C:N").EntireColumn.Hidden = True
If UCase(Target.Value) = "C2P" Then Range("C:C").EntireColumn.Hidden = False
If UCase(Target.Value) = "CS" Then Range("D:D").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4P" Then Range("E: E").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4S" Then Range("F: F").EntireColumn.Hidden = False
End If

[Murali_K]

i used the above code, still its unhiding 1 column though there is 4 values in range "B6:B137". my expectation is to Unhide columns, equal to number of values in range "B6:B137".

[Murali_K]

i used the above code, still its unhiding 1 column though there is 4 values in range "B6:B137". my expectation is to Unhide columns, equal to number of values in range "B6:B137".

Remove the End Select statement and paste the following code in:


If Not Intersect(Target, Changed) Is Nothing Then
Range("C:N").EntireColumn.Hidden = True
If UCase(Target.Value) = "C2P" Then Range("C:C").EntireColumn.Hidden = False
If UCase(Target.Value) = "CS" Then Range("D:D").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4P" Then Range("E: E").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4S" Then Range("F: F").EntireColumn.Hidden = False
End If

[p45cal]

try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B137")) Is Nothing Then
Columns("C:N").Hidden = True
If InStr(1, Target.Value, "C2P", vbTextCompare) > 0 Then Columns("D").Hidden = False
If InStr(1, Target.Value, "CS", vbTextCompare) > 0 Then Columns("E").Hidden = False
If InStr(1, Target.Value, "C4P", vbTextCompare) > 0 Then Columns("F").Hidden = False
If InStr(1, Target.Value, "C4S", vbTextCompare) > 0 Then Columns("G").Hidden = False
End If
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B137")) Is Nothing Then
Columns("D").Hidden = InStr(1, Target.Value, "C2P", vbTextCompare) = 0
Columns("E").Hidden = InStr(1, Target.Value, "CS", vbTextCompare) = 0
Columns("F").Hidden = InStr(1, Target.Value, "C4P", vbTextCompare) = 0
Columns("G").Hidden = InStr(1, Target.Value, "C4S", vbTextCompare) = 0
End If
End Sub

[MagPower]

Dim Changed As Range

With ActiveSheet
Set Changed = .Range("B6:B137")
End With

If Not Intersect(Target, Changed) Is Nothing Then
'Range("C:F").EntireColumn.Hidden = True
If UCase(Target.Value) = "C2P" Then Range("C:C").EntireColumn.Hidden = False
If UCase(Target.Value) = "CS" Then Range("D:D").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4P" Then Range("E:E").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4S" Then Range("F:F").EntireColumn.Hidden = False

If UCase(Target.Value) = "ALL" Then Range("C:F").EntireColumn.Hidden = True
If UCase(Target.Value) = "NONE" Then Range("C:F").EntireColumn.Hidden = False
End If

Set Changed = Nothing

Ok, I made a few modifications to the code. (It now works for me.) Replace the old code with the above.

=Russ

[Murali_K]

No Luck....Still its unhiding only one column
try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B137")) Is Nothing Then
Columns("C:N").Hidden = True
If InStr(1, Target.Value, "C2P", vbTextCompare) > 0 Then Columns("D").Hidden = False
If InStr(1, Target.Value, "CS", vbTextCompare) > 0 Then Columns("E").Hidden = False
If InStr(1, Target.Value, "C4P", vbTextCompare) > 0 Then Columns("F").Hidden = False
If InStr(1, Target.Value, "C4S", vbTextCompare) > 0 Then Columns("G").Hidden = False
End If
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B137")) Is Nothing Then
Columns("D").Hidden = InStr(1, Target.Value, "C2P", vbTextCompare) = 0
Columns("E").Hidden = InStr(1, Target.Value, "CS", vbTextCompare) = 0
Columns("F").Hidden = InStr(1, Target.Value, "C4P", vbTextCompare) = 0
Columns("G").Hidden = InStr(1, Target.Value, "C4S", vbTextCompare) = 0
End If
End Sub

[p45cal]

Supply a file with some sample data in column B.

[Murali_K]

Ran modified code. still its unhiding only one column. i am just thinking of another approach. how to unhide multiple column, if column heading is entered in "B6:B137"?


Dim Changed As Range

With ActiveSheet
Set Changed = .Range("B6:B137")
End With

If Not Intersect(Target, Changed) Is Nothing Then
'Range("C:F").EntireColumn.Hidden = True
If UCase(Target.Value) = "C2P" Then Range("C:C").EntireColumn.Hidden = False
If UCase(Target.Value) = "CS" Then Range("D:D").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4P" Then Range("E:E").EntireColumn.Hidden = False
If UCase(Target.Value) = "C4S" Then Range("F:F").EntireColumn.Hidden = False

If UCase(Target.Value) = "ALL" Then Range("C:F").EntireColumn.Hidden = True
If UCase(Target.Value) = "NONE" Then Range("C:F").EntireColumn.Hidden = False
End If

Set Changed = Nothing

Ok, I made a few modifications to the code. (It now works for me.) Replace the old code with the above.

=Russ

[Murali_K]

23720
Supply a file with some sample data in column B.

[MagPower]

What version of Excel are you using?

[Murali_K]

Yes... its resulting the same as your code.

[Murali_K]

2016
What version of Excel are you using?

[p45cal]

try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("B6:B137")
If Not Intersect(Target, Changed) Is Nothing Then
Columns("C:N").Hidden = True
For Each cll In Changed.Cells
If InStr(1, cll.Value, "C2P", vbTextCompare) > 0 Then Columns("D").Hidden = False
If InStr(1, cll.Value, "CS", vbTextCompare) > 0 Then Columns("G").Hidden = False
If InStr(1, cll.Value, "C4P", vbTextCompare) > 0 Then Columns("E").Hidden = False
If InStr(1, cll.Value, "C4S", vbTextCompare) > 0 Then Columns("F").Hidden = False
Next cll
End If
End Sub

[Paul_Hossler]

You realize that the last one in wins

23721



If i copy "C4P,C2P,C4S,CS" in "B6:B137" cells it should unhide "C", "D", "E" and "F" columns.


Did you mean D:G?




Option Explicit
'If i copy "C4P,C2P,C4S,CS" in "B6:B137" cells it should unhide "C", "D", "E" and "F" columns.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

If Intersect(Target, Range("B6:B137")) Is Nothing Then Exit Sub

s = UCase(Target.Cells(1, 1).Value)

Application.EnableEvents = False

Range("C:DN").EntireColumn.Hidden = True

If InStr(s, "C2P") > 0 Then Range("D:D").EntireColumn.Hidden = False
If InStr(s, "C4P") > 0 Then Range("E:E").EntireColumn.Hidden = False
If InStr(s, "C4S") > 0 Then Range("F:F").EntireColumn.Hidden = False
If InStr(s, "CS") > 0 Then Range("G:G").EntireColumn.Hidden = False
Application.EnableEvents = True
End Sub



You can add the 'ALL' and 'NONE' option

[Murali_K]

Wow.........This code is working. Thanks a lot.... You are awesome....
Just one more question,
i guess, if i have to add any additional columns, i can replace the column name in "If InStr(1, cll.Value, "C4S", vbTextCompare) > 0 Then Columns("F").Hidden = False" right?
try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("B6:B137")
If Not Intersect(Target, Changed) Is Nothing Then
Columns("C:N").Hidden = True
For Each cll In Changed.Cells
If InStr(1, cll.Value, "C2P", vbTextCompare) > 0 Then Columns("D").Hidden = False
If InStr(1, cll.Value, "CS", vbTextCompare) > 0 Then Columns("G").Hidden = False
If InStr(1, cll.Value, "C4P", vbTextCompare) > 0 Then Columns("E").Hidden = False
If InStr(1, cll.Value, "C4S", vbTextCompare) > 0 Then Columns("F").Hidden = False
Next cll
End If
End Sub

[p45cal]

i guess, if i have to add any additional columns, i can replace the column name in "If InStr(1, cll.Value, "C4S", vbTextCompare) > 0 Then Columns("F").Hidden = False" right?Yes

[Murali_K]

you are right. I could have mentioned it clearly in my question.Your code giving another possibility. Thanks for your help.
You realize that the last one in wins

23721



Did you mean D:G?




Option Explicit
'If i copy "C4P,C2P,C4S,CS" in "B6:B137" cells it should unhide "C", "D", "E" and "F" columns.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

If Intersect(Target, Range("B6:B137")) Is Nothing Then Exit Sub

s = UCase(Target.Cells(1, 1).Value)

Application.EnableEvents = False

Range("C:DN").EntireColumn.Hidden = True

If InStr(s, "C2P") > 0 Then Range("D:D").EntireColumn.Hidden = False
If InStr(s, "C4P") > 0 Then Range("E:E").EntireColumn.Hidden = False
If InStr(s, "C4S") > 0 Then Range("F:F").EntireColumn.Hidden = False
If InStr(s, "CS") > 0 Then Range("G:G").EntireColumn.Hidden = False
Application.EnableEvents = True
End Sub



You can add the 'ALL' and 'NONE' option

[Murali_K]

Thank you very Much p45cal,MagPower and Paul_Hossler for your help.
Have a great day Guys.................:hifive::hifive::hifive: