Drop Down Change update Hidden Columns

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
Are you saying that you want to hide the columns only in "Stations" sheet?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
Try:
VBA Code:
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

Board Regular
Joined
Aug 17, 2011
Messages
118
Try:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,306
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top