Using a macro to selectively hide rows -- with a wrinkle

[saluja04]

Hello,

I am trying to construct a macro to hide a row if its Column E cell has a) a value greater than 14, or b) an error (specifically a #num! error).

The spreadsheet I am working on is a list of birthdays. Columns A and B contain names; column C contains the mm/dd/yyyy birthdate; column D contains the birthday without the year; column E uses the following formula to calculate the number of days until the person's next birthday (this was taken out of cell E2):

=IF((DATEDIF(TODAY(),D2,"YD")=0),"today",IF(DATEDIF(TODAY(),D2,"YD")<0,DATEDIF(D2,TODAY(),"YD"),DATEDIF(TODAY(),D2,"YD")))

Basically, if the difference between the birthday (without years) is 0, display "Today", otherwise give the number.

I want a macro that will first sort everyone in order of number of days until their birthday, and then hide everyone who's birthday is more than two weeks away.

The reason I mention the error is that in calculating the number of days until the person's birthday, the most effective way I found to get it done was to first convert the mm/dd/yyyy into mm/dd using the =TEXT(cell,"mm/dd") formula, then take the difference between that day and today. Since the DATEDIF function does not allow a negative number of days, it shows a #num! error.

I have a macro that will hide each row if the birthday is more than two weeks away, but it runs into problems once it sees the #num! error, and it takes a long time (I have nearly 400 records). Shouldn't it be possible, since they'll all be sorted anyway, to just find birthdays at least 15 days away and hide everything after that?

Thank you for the help. I am running xl2003 on an XP machine, but have access to 2007/Vista. I would like to be able to use it on the xl2003/XP machine, though.

 

[Anthony47]

Why don't you just use Filter?
The days are calculated in col E, Select column E, Menu /Data /Automatic Filter; then from the Filter pull down menu choose Customize, less then, 14, Ok. Only those line that have col. E lower than 14 will be displayed.
If you wish you can record a macro while you:
-sort the table by colum E
-apply such a filter
-end record
Then you assign this macro a shortcut (like Contr-shift-b) and the macro will run wenever you press that key-combination.

Bye.

 

[Anthony47]

And may I suggest this formula to calculate the days to next birthday:

=IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))<TODAY(), DATE(YEAR(TODAY())+1,MONTH(C2)DAY(C2)),DATE(YEAR(TODAY()),MONTH(C2), DAY(C2)))-TODAY()

Bye.