View Full Version : [SOLVED:] VBA code to unhide Unhide multiple columns based on multiple values in row
Murali_K
02-10-2019, 10:38 PM
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
02-11-2019, 02:03 AM
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
02-11-2019, 03:13 AM
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
02-11-2019, 05:19 AM
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
02-11-2019, 05:36 AM
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
02-11-2019, 06:06 AM
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
02-11-2019, 09:29 AM
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
02-11-2019, 09:31 AM
Supply a file with some sample data in column B.
Murali_K
02-11-2019, 09:32 AM
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
02-11-2019, 09:40 AM
23720
Supply a file with some sample data in column B.
MagPower
02-11-2019, 09:45 AM
What version of Excel are you using?
Murali_K
02-11-2019, 09:47 AM
Yes... its resulting the same as your code.
Murali_K
02-11-2019, 09:50 AM
2016
What version of Excel are you using?
p45cal
02-11-2019, 09:51 AM
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
02-11-2019, 09:55 AM
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
02-11-2019, 09:59 AM
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
02-11-2019, 10:01 AM
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
02-11-2019, 10:08 AM
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
02-11-2019, 10:14 AM
Thank you very Much p45cal,MagPower and Paul_Hossler for your help.
Have a great day Guys.................:hifive::hifive::hifive:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.