Solved: vba to filter report based on date/month

[aloy78]

Hi all,

I would like to run 3 code based ondates/month. I know the auto filter itself does this. But I want to have a morefasterway and more professional look to thereport :)

The first code will (upon click on Cell"E7"):
- show all hidden columns & rows (in case i havemistakenly hidden them, which I always do ;P)
- then filter the cell "Report Date" for data thatwere of last month

Thesecond code(upon click on cell "E8") will does the same function asthe first code except it will filter off data that is off the current month.

Thethrid code (upon click on cell "E9") will prompt out a user form. Theform will require us to key in the start date and end date. Let's say forexample, I would like to search for records in May. So I just key in 01/05/2011and 31/05/2011. Maybe can add in syntax like <, >, =, <=, >=.


I've attached a file for your reference.

[Bob Phillips]

This worksheet event code handles the first two. I'll leave the third to you, it is beyond the threshold of fair free help in my view.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrow As Long
Dim rng As Range

Select Case Target.Address

Case "$E$7"

With Me

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B18").Resize(Lastrow - 17)
rng.AutoFilter Field:=1, _
Criteria1:=xlFilterLastMonth, _
Operator:=xlFilterDynamic
End With

Case "$E$8"

With Me

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B18").Resize(Lastrow - 17)
rng.AutoFilter Field:=1, _
Criteria1:=xlFilterThisMonth, _
Operator:=xlFilterDynamic
End With
End Select
End Sub

[aloy78]

Hi xld,

Thank you very much for the code. Works nicely :clap:

I'll try to ask around for the third :think: Well, maybe it wouldn't be called fair for such request, I do admit. But thanks anyway. Your help has contributed a lot to me for all the past request.