Hide Columns Based on Date Selection

**overbomb** · 02-28-2013, 10:35 AM

Hello,

I am a novice when it comes to macro's and I wondered if it is possible hide certain columns based on a month selection.

On the attached the document, cell D6 has month slection.
Each month contains 3 columns, e.g. row 7, cell F7 'Oct-12 Act', cell G7 'Oct-12 Bud, cell H7 'Oct-12 Var'. This form holds across all months, up to Feb-13 in this example

For example, if I select 'Jan-13' from cell D6, ideally what I would like to appear is Oct-12 Act, Nov-12 Act, Dec-12 Act, Jan-13 Act, Jan-13 Bud, Jan-13 Var, Feb-13 Act

Does that makes sense - I have highlighted the columns I'd like to appear based on Jan-13 being selected in Green. Alternatively, if Feb-13 were selected in cell D6 I have highlighted what I would like to appear in Blue

If anyone can help that would be amazing!!

And, if macro is possible, then to have a button near cell D6 to run the macro

Let me know when you can

Many thanks, Maddy

**Mumps1** · 02-28-2013, 12:01 PM

Hello Maddy. The first macro will display the desired columns based on the date in D6 for Jan. and Feb. You will have to add additional 'ElseIf' statements to deal with the other months. Just follow the pattern making the appropriate changes to the column ranges and making sure you use the full date and not the date as it appears in D6. The second macro will show all the columns again. You can insert two buttons and assign each macro to one of the buttons.

Please Login or Register  to view this content.

**overbomb** · 02-28-2013, 12:21 PM

Hi

Thank you for replying.

The macro hides the columns irrespective of cell D6 selection, perhaps it needs to be layered up - I'm not sure. I'll investigate more myself and have a play around with it, see what pops out

Maddy

**overbomb** · 03-06-2013, 06:10 AM

ok,

i've extended the marco but it's now coming back with an error on ActiveSheet.Range("D:AB).EnitreColumn.Hidden = True (see below)

is this because I've extended it? I've played round with column selection but can't seem to debug

Sub HideCol()
Application.ScreenUpdating = False
ActiveSheet.Range("D:AB").EntireColumn.Hidden = True
If ActiveSheet.Range("C6").Value = "01/10/2012" Then
ActiveSheet.Range("D:D,E:E,F:F,H:H,I:I,J:J,K:K,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/11/2012" Then
ActiveSheet.Range("D:D,F:F,G:G,H:H,I:I,J:J,K:K,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/12/2012" Then
ActiveSheet.Range("D:D,F:F,H:H,I:I,J:J,K:K,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/01/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,K:K,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/02/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,M:M,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/03/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,O:O,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/04/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,Q:Q,R:R,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/05/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,S:S,T:T,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/06/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,U:U,V:V,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/07/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,W:W,X:X,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/08/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Y:Y,Z:Z,AB:AB").EntireColumn.Hidden = False
ElseIf ActiveSheet.Range("C6").Value = "01/09/2013" Then
ActiveSheet.Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Y:Y,Z:Z,AA:AA,AB:AB").EntireColumn.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Sub UnhideCol()
Range("F:AC").EntireColumn.Hidden = False
End Sub