Autofilter question

[rickblunt]

Greetings, I think that this may be any easy question and that I am just having a brain freeze or something. But I have a list of 400 people in column A and their birthdays is column B. So using the autofilter to sort out the dates is easy enough, but I would like to be able to sort out by week or month. For example 5 or 6 months from now, I pull up the spreadsheet and I want to see the birthdays for the upcoming week. I tried typing in "today()" in the custom filter box but that did not work so I tried this in a macro.

Sub WeekSearch()
    Selection.AutoFilter Field:=1, Criteria1:="Today()"
End Sub

Same result, (probably because there are only dates in the cells and not "Today()", right?)


Perhaps I am going about this in entirely the wrong way - I appreciate any input on how I should be doing this? Thanks, RB

 

[Simon Lloyd]

Anyway, why a VBA solution? surely you can just click the autofilter, choose custom and set the greater than equals to {date of your choice} and the same with the next box less than equals to.

It will then provide the filter you need.

 

[rickblunt]

yes, and I changed the adder to coincide with a real birthday and nothing happened - nothing got filtered...

 

[rickblunt]

Thats true, but I would have to do the custom filter process everytime I search the dates - I was hoping to come up either with a custom filter that I could save in the drop-down, or a macro that I could assign to a control button. One clisk and I would be done kind of thing...

 

[Simon Lloyd]

Content removed by Moderator

 

[rickblunt]

That's cool, I really appreciate the effort that you have put in to this for me. I want to double check a little more to make sure that I am not messing something up that you have given me, and then I will post it over there - thanks, RB

 

[rickblunt]

Hey Simon, I just did what I normally do when one of you gurus give me code that I know must work, so I figure I am doing something wrong - I put it in to a brand new WB and viola! your macro worked perfectly. So now I just have to figure out why it doesn't work in my existing book... I just thought of something, maybe I need to turn the autofilter off first?

 

[Simon Lloyd]

Nope its not turning it off, i think perhaps your dates arent really dates but are text or did you copy them from somewhere?

 

[rickblunt]

on the new WB I just copied and pasted the cells from the old WB to the new WB. I turned off the AF, and now I am getting a error 13, type mismatch error - checking that to see if I can track it down. I will double check the cell formatting to make sure that it is dates.

I will say that in my original WB I have data in columns A:G, whereas the new WB only had data in B:C, not sure if that is a problem or not - I wouldnt think so...

 

[Simon Lloyd]

The code i gave you that shows a message box will work if it was 2 columns wide or 200 columns wide!.

It does sound like either the format is wrong or you have leading or trailing spaces.

 

[Simon Lloyd]

When you finally do get it sorted that they are dates this is the Autofilter solution!

Sub filter_dates()
Dim date1 As Long, date2 As Long, dDate1, dDate2
dDate1 = Date
dDate1 = DateSerial(Year(dDate1), Month(dDate1), Day(dDate1))
dDate2 = Date + 7
dDate2 = DateSerial(Year(dDate2), Month(dDate2), Day(dDate2))
date1 = dDate1
date2 = dDate2
    Columns("B:B").AutoFilter Field:=1, Criteria1:=">=" & date1, Operator:=xlAnd, Criteria2:="<=" & date2
End Sub