Autofilter question

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
486
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.

Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
This is what you need:
Code:
Sub WeekSearch()
    Selection.AutoFilter Field:=1, Criteria1:=Date + 7
End Sub
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
486
nah, that doesn't return anything when I run it- the macro as you wrote it would need to know what "date" was anyway. Maybe I am mis-interpreting what you are writing? Thanks for trying though...
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
It does return values when i run it on a column with dates!!!
Excel knows what DATE is because in VBA DATE is Today!!
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

Maybe it doesn't return (filter) anything because there aren't any birthdays 7 days from now!
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
486
no, that would not be the case, first thing I would check - I have birthdays on the 5, 7, 8, 9, 10, 13,...
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

Run this code:
Code:
Sub WeekSearch()
Dim MyCell As Range, Rng As Range, msg As String
Set Rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If CDate(MyCell.Value) > Date And CDate(MyCell.Value) < Date + 7 Then
msg = msg & "Bithday for " & MyCell.Offset(0, 1).Value & " on this date " & MyCell.Value & vbLf
End If
Next MyCell
MsgBox msg
End Sub
Assuming dates are in column B and names in column C
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
no, that would not be the case, first thing I would check - I have birthdays on the 5, 7, 8, 9, 10, 13,...
As i said, for the previous solution, you dont have a birthday 7 days from now as that would be the 12th!
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
486
OK, that makes sense - but what I was trying to filter was all of the birthdays for the upcoming week....
 

Watch MrExcel Video

Forum statistics

Threads
1,133,559
Messages
5,659,547
Members
418,506
Latest member
drafting3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top