Automatically Hiding Columns Based on relative cell value

[danielthomas]

Hi there - using excel 10 on windows 7.

I am relatively proficient in general excel use but completely lost when it comes to VBA. I have come across a number of answers to parts of this and have tried for several hours to get it working but to no avail and so would apprecaite any help given

I have a table of data in D5:AH33 This data automatically populates after running a macro. Each column is headed in row 5 with a date. eg. D5 = 1/2/14, D6 = 2/2/14 etc.

I wish to have all of the columns hidden (within the range D:AH) except the column with yesterday's (TODAY()-1) date in row 5.

However.....

Cell AN1 contains a dropdown list of months and when a given month is chosen it pulls through data for that month and populates the table above. I wish the above hiding to only take affect if the value in AN1 is the current month.This is so that anyone looking at previous month's data can see the whole months, not just find everything hidden!!

Finally I would like to be able to place a button on screen (in cell AQ1) which toggles between all of the current month being shown and the script of hiding all but the current date column. So that if i wish to view the whole of this month's data I can do so.


Is any/all of this possible?

Many many thanks


Dan

 

[XLfriend777]

Hi danielthomas - As nobody has answered yet I thoght I would get things started. Welcome to the forumn and to VBA. If you are relatively proficient with excel, you will love VBA. The key is to break things into small, achievable goals, and then string them together. With that in mind I thought I would get started with the code below. It simply hides ALL the columns D:AH. I then picked column E as an example to "Unhide". We can then go on to accomplish the other tasks. Try the code below. Hope this helps.

Sub danielthomas_Hide_Columns()
Range("D:AH").Select
Selection.EntireColumn.Hidden = True
Range("E:E").Select
Selection.EntireColumn.Hidden = False
End Sub

 

[danielthomas]

Hi goesr,

Thank you for your post.

I have posted this into the sheet's code but when i run it nothing happens. Well the excel seems to refresh but nothing hides/unhides.


Thanks.

D

EDIT: Also have played around with the code and can get some cells to hide but not a range and then speciifcally not a column within that range

 

[XLfriend777]

Hi danielthomas - You need to run that macro from inside a Module. Press Alt+F11 which will open the Macro Editor and your file needs to be saved as macro-enabled with a .xlsm extension. Check out the information at:

Run a macro - Excel - Office.com

Hope this helps.