Autofilter question

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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
Maybe it doesn't return (filter) anything because there aren't any birthdays 7 days from now!
 

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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
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

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
OK, that makes sense - but what I was trying to filter was all of the birthdays for the upcoming week....
 

Forum statistics

Threads
1,176,401
Messages
5,902,840
Members
435,000
Latest member
jmc12

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