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".

Originally Posted by 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

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

Originally Posted by 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

**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"?

Originally Posted by 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** · 02-11-2019, 09:40 AM

SLC.xlsm

Originally Posted by p45cal

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

Originally Posted by MagPower

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

Capture.JPG

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?

Originally Posted by 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

**p45cal** · 02-11-2019, 10:01 AM

Originally Posted by Murali_K

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.

Originally Posted by Paul_Hossler

You realize that the last one in wins

Capture.JPG

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

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

Thread Tools

Display

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

Posting Permissions