VBA Hide/Unhide columns based on ROW value

[Fraser101]

Hi,

I have a row A3:AZ3 where I've marked certain cells Y for the columns that I'd like to hide/unhide. I've marked cells as N that I don't want to hide.

I think the tricky part is that it will eventually be a button so that the columns will hide/unhide.

I've tried using the macro recorder, help and these pages but I can't seem to get what I need.

This is what I've got so far (took from another thread) but I stress I don't know if its even close or will make any sense.


Sub HideUnhideColumns()
If Range("A3:AZ3") = "Y" Then
Range("A3:AZ3").Rows.Hidden = True
Range("A3:AZ3").Rows.Hidden = False

End If
If Range("A3:AZ3") = "N" Then
Range("A3:AZ3").Rows.Hidden = False
Range("A3:AZ3").Rows.Hidden = True

End If
End Sub

PLEASE HELP

 

[mikerickson]

You have to test each cell in row 3.

Sub HideAccordingToRow3 ()
    Dim oneCell as Range

    For Each oneCell in Range("A3:AZ3")
        oneCell.EntireColumn.Hidden = (LCase(CStr(oneCell.Value)) <> "y")
    Next oneCell
End Sub

 

[VoG]

Try this

Sub HideUnhideColumns()
Dim i As Long
For i = 1 To 52
    Columns(i).Hidden = Cells(3, i).Value = "Y"
Next i
End Sub

 

[Fraser101]

Hi, the second suggestion above does work. Mike I think my explanation may not have been clear, it works but the wrong way!

The part I need to add is

Columns("A:AZ").Hidden = False

but I need to add this within the same macro so that it can all be in one button, is this possible or will I need 2 buttons?

 

[Rick Rothstein]

Assuming your Y's and N's are text constants and not formulas, this macro should do what you want...

Sub HideRow3CellsWithY()
  With Range("A3:AZ3")
    .Columns.Hidden = False
    .Replace "Y", "=Y", xlWhole
    With .SpecialCells(xlCellTypeFormulas)
      .EntireColumn.Hidden = True
      .Replace "=", "", xlPart
    End With
  End With
End Sub

 

[johnlucasprice]

I'm working on something similar. I have an input tab that is referenced in about 30 tabs where I want to hide columns depending upon what quarter I'm working on. I only want to show the YTD actual columns. If I want to see Q1 data only, I need to hide the remaining 9 columns (months). It works, but only when I PHYSICALLY change the value of $N$1. The kicker is that $N$1 is a lookup from my main input tab since I'm going to have many tabs with similar data. Anyone know how to make this work automatically when I change my main input tab? So close, yet so frustrated. Maybe I shouldn't have it in the worksheet code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$1" Then

If LCase(Target.Value) = 1 Then
Columns("S:AA").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = 2 Then
Columns("V:AA").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = 3 Then
Columns("Y:AA").EntireColumn.Hidden = True
Else
Columns("S:AA").EntireColumn.Hidden = False
End If

End If

End Sub

 

[mikerickson]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$1" Then

    Columns("S:AA").EntireColumn.Hidden = False
    Select Case Val(Cstr(Target.Value)
        Case 1
            Columns("S:AA").EntireColumn.Hidden = True
        Case 2
            Columns("V:AA").EntireColumn.Hidden = True
        Case 3
            Columns("Y:AA").EntireColumn.Hidden = True
    End Select
End If

 

[johnlucasprice]

Tab 1 is an input tab. Tabs 2 to 31 are data tabs. Since cell $N$1 on tabs 2 to 31 is a formula linked to my input tab I'm not going to change the value of $N$1 directly. I'm going to go to my input tab and enter a value that will flow through tabs 2 to 31. When I navigate back to tab 2 I would like the columns to be hidden appropriately automatically. That's not happening now. What do I change to make the hiding automatic if my target value is a formula?

 

[mikerickson]

Instead of individual Change events you could use a Workbook_SheetChange event in the ThisWorkbook code module. The range variable precedentCells is those cells in Tab1 that feed $N$1 of the other sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oneSheet As Worksheet
    Dim precedentCells As Range
    Set precedentCells = Worksheets("Tab1").Range("A1,B3,C2")

    If Sh.Name = "Tab1" Then
        If Not Application.Intersect(Target, precedentCells) Is Nothing Then
    
            For Each oneSheet In Worksheets(Array("Tab2", "Tab3"))
                With oneSheet
                    .Range("S:AA").EntireColumn.Hidden = False
                    Select Case Val(CStr(.Range("N1").Value))
                        Case 1
                            .Range("S:AA").EntireColumn.Hidden = True
                        Case 2
                            .Range("V:AA").EntireColumn.Hidden = True
                        Case 3
                            .Range("Y:AA").EntireColumn.Hidden = True
                    End Select
                End With
            Next oneSheet
        End If
    End If
End Sub

 

[johnlucasprice]

Mike, thanks but I can't get that to work because I want to rename my tabs. The issue is that I'm building a template file where tab 2 to tab "n" will be for individual buildings, so they could be named anything from "Admin Bldg" to "Wash HS" to "Lincoln Elem". If the template is used for 100 different customers, some will have 5 buildings, some will have 50 buildings. Is there a simple way I can put the code into a template tab 2, then whether I copy that tab 5 times or 50 times and rename each tab with the building name, it won't matter? I just want the code in a template tab 2 that will automatically hide columns when I change tab 1 ("Report Setup", aka input tab). See my original code. I was very close, but it wouldn't automatically hide columns unless I clicked in the tab 2 cell that was linked to tab 1. Is it a difference between "activate", "calculate", "change", "selection change", etc.?