Hide and Unhide Columns Based on Value in Excel 2007 and Excel 2010

Hi,

assuming that in cell A1,  you type a date: Jan 1st , Feb 1st, ... Dec 1stand you have the desired dates in columns A up to AE

now,

(possible) hidden columns are: AC, AD, AE

depend on days 28-29-30

also, say that the dates are on sheet1

right click on sheet1 tab, select view code and paste in the following:

[Edit..]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

Range("AC:AE").EntireColumn.Hidden = False

If Day(Range("A1")) <> 1 Then Exit Sub

x = DateSerial(Year(Range("A1")), Month(Range("A1")) + 1, 1) - Range("A1")

Select Case x

Case Is = 28

Range("AC:AE").EntireColumn.Hidden = True

Case Is = 29

Range("AD:AE").EntireColumn.Hidden = True

Case Is = 30

Range("AE:AE").EntireColumn.Hidden = True

Case Else

Exit Sub

End Select

End If

End Sub

I am wanting to do the same thing, but all my days of the month are across rows instead of columns.  Can I just switch out all references to "Column" and switch to rows?  (I will also update target and ranges.)

is beyond your knowledge scope.

I interpret your remark as sarcastic and offensive. Request that you edit it out.

The columns can be hidden with VBA. However, if you already have code to remove the unneeded days then please post it because it may be able to be modified to hide the columns and even if not practical to modify then it should help to confirm exactly what you are trying to achieve because I should have thought that whether the columns are hidden or just not showing a column header for the invalid dates after end of month would be irrelevant.

Unless I am mistaken, it seems that your suggestion simply removes unneeded days. A macro has been created to do that already. Perhaps what is desired either cannot be done within VBA or is beyond your knowledge scope.

I am assuming that your comment "Below that, the days of the month are displayed (1-31)." you mean they are displayed across the worksheet in the next row because you refer to hiding columns. Is this correct?

I am also assuming that because this is a Programming forum that you anticipate VBA code to achieve this. How about just doing it with formulas and display the number of days for the particular month and with the use of a formula insert a zero length string for the days from 29 to 31 as appropriate so that the cells appear to be blank (although they actually contain a formula)

Try this on a blank worksheet. (I will use the alpha when referring months in the description because I do not know whether you are in a d/m/y date region or m/d/y region. This will not affect the formulas.)

In cell A1 enter any date that is the first day of the month. eg   1 Feb 2013

For cell A1 use Number format and create a Custom format to display the date as "mmm-yy"  eg. If you entered date as per previous step it should display as  Feb-13 and although it is really a date it only displays the month and year.

Select the range A2:AE2 (next row) and use Number format and create a Custom format for the selected area to display the date as "dd"  ie. the cell will be containing a date but will only display the day portion.

In cell A2 enter the formula    =A1

In cell B2 enter the formula    =A2+1

Copy the formula from A2 across the row to cell AB2

In cell AC2 enter the formula      =IF(MONTH(AB2+1)=MONTH(A1),AB2+1,"")

In cell AD2 enter the formula      =IF(MONTH(AB2+2)=MONTH(A1),AB2+2,"")

In cell AE2 enter the formula       =IF(MONTH(AB2+3)=MONTH(A1),AB2+3,"")

Now test by selecting cell A1 and in the formula bar alter the month and/or year. The number of days displayed in row 2 should alter depending on the month and year in cell A1. Leap years are accounted for so test with Feb in leap years also.

You should be able to copy the range from A1: AE2 (on 2 rows) and paste it anywhere you like on a worksheet and it should work because Excel will adjust the relative references in the formulas.

Feel free to get back to me if you have any problems.