Filter by month (not as a string) using VBA

[Margate]

Hello everyone,

I am trying to get VBA to filter based on the month. So a user inputs "March" into cell R2 and cell R1 has the following formula in:

=MONTH(1&R2)

this equals to 3 (March). VBA will take the value of cell R1 (3 (March)) and filter column B for any entries with March in.

I am attaching a spreadsheet in case my explanation does not make much sense. There is a small VBA code in the sheet that filters by the month as a string but it does not work because the dates in column B are not entered as strings.

Thank you for any help,

Margate

[jindon]

1) Select A1:O1 [Unmerge]
2) Delete row(2), merged cell(s) are just creating problems in data manipulating process.
3) Clear R1:R2, enter formula
=MONTH(B2)=3
in R2
4) Select A1 and [Data] - [Advanced]
5) Select R1:R2 for [Criteria Range] then hit [OK]

[Maudibe]

Alternately, how about a helper column (example: P3=MONTH(B3) then copy down).

Change code line from

ActiveSheet.Range("$B$3:$B$2002").AutoFilter Field:=2, Criteria1:="=*" & Sheets("Bookings").Range("R2").Value & "*", Operator:=xlAnd

to

ActiveSheet.Range("$A$1:$P$1517").AutoFilter Field:=16, Criteria1:=Sheets("Bookings").Range("R1").Value

This will filter all records to the month number calculated in R1.

HTH,
Maud

[jindon]

Margate

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Maudibe]

Margate,

Thank you as well for the rep and you are most welcome.

Maud