filter by month using VBA

[snowman1976]

hello
I have this simple bit of a macro that I use to filter information based on a date the user inputs into cell H1.
This works fine, but I am currently using formula's in A1 and B1 as 'helpers' to my macro. I would like to see what can be changed so A1 and B1 can be eliminated, and H1 is the only one it needs. Right now H1 is just the user inputting a date so I know what month they are looking for - so in other words if you input any date in April the range will become 4/1/2016 to 4/30/2016

cell A1=DATE(YEAR(H1),MONTH(H1),1)
cell B1=DATE(YEAR(H1),MONTH(H1)+1,1)-1


the code is as follows. Does anyone know how I can get the same result without the two helping cells?
Any help would be appreciated

startdate = Sheets("master").Range("a1")
EndDate = Sheets("master").Range("b1")

Criteria1 = ">=" & startdate
Criteria2 = "<=" & EndDate
Sheets("PROD_ACT").Select
ActiveSheet.Range("A:O").AutoFilter Field:=5, Criteria1:=Criteria1 _
, Operator:=xlAnd, Criteria2:=Criteria2

End Sub

 

[dmt32]

Hi,
give following a try & see if does what you want.

    Dim StartDate As Long, EndDate As Long
    
    With Sheets("master").Range("H1")
        StartDate = DateSerial(Year(.Value), Month(.Value), 1)
        EndDate = DateSerial(Year(.Value), Month(.Value) + 1, 0)
    End With


    
    Sheets("PROD_ACT").Range("A:O").AutoFilter Field:=5, _
                                                Criteria1:=">=" & StartDate, _
                                                Operator:=xlAnd, _
                                                Criteria2:="<=" & EndDate

Dave

 

[snowman1976]

thanks Dave, worked like a charm!