Hide column based on cell value

[olympiac]

I am using the code below to hide a column based on a cell value.
I need to repeat this macro from column C to column AF.
How do I need to modify it to avoid copying this code for each colum?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C5").Value = 0 Then
Columns("C").EntireColumn.Hidden = True
Else
Columns("C").EntireColumn.Hidden = False
End If
End Sub


Thanks

 

[olympiac]

Great. Thank you very much!

 

[Sektor]

Not at all!

 

[RobertCotton]

I guess you need Worksheet_Change instead of Worksheet_SelectionChanged. Correct?

Private Sub [B][COLOR="Red"]Worksheet_Change[/COLOR][/B](ByVal Target As Range)
    If Range("C5").Value = 0 Then
        Columns("C:AF").EntireColumn.Hidden = True
    Else
        Columns("C:AF").EntireColumn.Hidden = False
    End If
End Sub

Good Morning.

I have been searching the site to find code for a macro similar to this one but this one comes closest to my needs. However, what I would like to do is hide columns J and K on "Sheet1" of my workbook if the value in cell D6 on "Sheet2" = "N". Would it be possible to adapt this code to achieve my result?

Thank you for your help.

 

[jbdown]

Sektor -- thanks for this code. How would the code change if I wanted to hide all columns that had the value of either 1 or 2 in row 5 (as opposed to the value of 0 as in this example)? Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    For i = 3 To 32
        Cells(1, i).EntireColumn.Hidden = Cells(5, i) = 0
    Next
End Sub

Or this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    For i = 3 To 32: Cells(1, i).EntireColumn.Hidden = Cells(5, i) = 0: Next
End Sub

 

[noletwin1]

I am trying to hide columns based on the first cell in every column. The cell value changes in cell 1 of every column based on drop down menus. Can anyone help?

 

[Rick Rothstein]

I am trying to hide columns based on the first cell in every column. The cell value changes in cell 1 of every column based on drop down menus. Can anyone help?

Of course we can help... just tell us what the condition in that first cell needs to be in order that its column should be hidden (and also tell us which columns should be monitored).

 

[Rick Rothstein]

Of course we can help... just tell us what the condition in that first cell needs to be in order that its column should be hidden (and also tell us which columns should be monitored).

While I am waiting for noletwin1 to respond to the question I asked in Message #16, I thought future readers of this thread might find this non-looping one-liner (it's a long one) that I came up with for the original question that started this thread to be of interest...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Range(Replace(Application.Trim(Join(Evaluate("IF(C5:AF5=0,SUBSTITUTE(ADDRESS(5,COLUMN(C:AF),4)&"":""&ADDRESS(5,COLUMN(C:AF),4),5,""""),"""")"))), " ", ",")).EntireColumn.Hidden = False
End Sub[/td]
[/tr]
[/table]

 

[noletwin1]

Thanks for your help. I got the macro done. Now I need to know how to assign to run when any option is chosen in a drop down menu.

 

[Rick Rothstein]

Thanks for your help. I got the macro done. Now I need to know how to assign to run when any option is chosen in a drop down menu.

You would use the worksheet's Change event having the Target monitor the cells containing your dropdowns. Normally, one would include the code in the event procedure directly rather than calling out to a macro, but it will work the way you are apparently approaching it. Normally, I (or others here) would provide code for you, but since you have not provided any details for us, we cannot help you with that part of your task.

 

[noletwin1]

I used the below to macro to do what I needed, but need this to run every time Cells C3, C4, and C5 change.

Sub HideColumns()
Columns("G:FA").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = False
For Each cll In Range("E1:FA1")
If (cll.Value = 0) And (cll.Value <> "") Then cll.EntireColumn.Hidden = True
Next cll
Application.ScreenUpdating = True
Range("C4").Select
End Sub