Macro Help Needed

[Ameadows21]

I have a spreadsheet that has a column for each month of the year. Each column has several rows of data. I can't figure how to properly code a macro so that selecting March from a drop down will hide all the columns except march. Can anyone help me in write this code?


Thanks!

 

[Luke M]

Right click on sheet tab, paste this in. Modify as needed:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xDrop As Range

'Where is the dropdown?
Set xDrop = Range("A1")
If Intersect(Target, xDrop) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

'loop through each column, assuming names are in B:M
For Each c In Range("B1:M1")
c.EntireColumn.Hidden = Not (UCase(c.Value) = UCase(xDrop))
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Ameadows21]

Perfect...thank you for the quick response!

 

[Luke M]

Glad I could help. =)

 

[Istiyak]

Hey LukeM,


Amazing, always learn something new from u dear,...


Regards

!$T!

 

[Ameadows21]

Currently when my drop down is empty, the spreadsheet hides all the columns. Is there a way I can make all the columns appear?


Thanks!

 

[Hui]

[pre]

Sub Unhide_All_Columns()
Range("A:A", Selection.End(xlToRight)).EntireColumn.Hidden = False
End Sub

[/pre]

I don't use Column XFD as the above will work with all excel versions