Drop Down Change update Hidden Columns

[Grimm127]

Hi Guru's

I have a Drop Down List via Data Validation in Cell B1
The list consists of a Date range by month

I would like that every time this drop down is changed to another month that it will call a hidden column sub. But since its only in that one worksheet
the code below only needs to ref "Stations" as a ws. Any Help would be great! Thanks in advance.

Sub Hide_Columns_Via_CoverPage()

' Hides Columns that are not within the Period

Application.ScreenUpdating = True

Dim ws As Variant, Xrow As Long, LCol As Long

For Each ws In Worksheets(Array("Stations", "Commercial", "Call Center", "HR", "Balance Sheet", "Cash Flow Statement"))

Xrow = 5
LCol = ws.Cells(5, Columns.Count).End(xlToLeft).Column

    With ws
        For x = 1 To LCol
            If ws.Cells(Xrow, x) <> "X" Then
                ws.Columns(x).Hidden = True
            Else
                ws.Columns(x).Hidden = False
            End If
        Next x

    End With

Next ws

Application.ScreenUpdating = False

End Sub

 

[mumps]

Are you saying that you want to hide the columns only in "Stations" sheet?

 

[Grimm127]

Are you saying that you want to hide the columns only in "Stations" sheet?

Yes. I know there is a way to do it within the actual sheet but I never did it

 

[mumps]

Try:

Sub HideColumns()
    Application.ScreenUpdating = False
    Dim LCol As Long
    With Sheets("Stations")
        LCol = .Cells(5, Columns.Count).End(xlToLeft).Column
        For x = 1 To LCol
            If .Cells(5, x) <> "X" Then
                .Columns(x).Hidden = True
            Else
                .Columns(x).Hidden = False
            End If
        Next x
    End With
    Application.ScreenUpdating = True
End Sub

 

[Grimm127]

Try:

Sub HideColumns()
    Application.ScreenUpdating = False
    Dim LCol As Long
    With Sheets("Stations")
        LCol = .Cells(5, Columns.Count).End(xlToLeft).Column
        For x = 1 To LCol
            If .Cells(5, x) <> "X" Then
                .Columns(x).Hidden = True
            Else
                .Columns(x).Hidden = False
            End If
        Next x
    End With
    Application.ScreenUpdating = True
End Sub

It doesn't do anything. Also, I want it to work when the list is changed to another date

 

[mumps]

I tested the macro on a dummy file and it worked properly. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also explain in detail what you mean by this:

I want it to work when the list is changed to another date

To avoid clutter, click the "Reply" button not the "+Quote" button when you respond.