Filtering Month & Year Excel VBA

[primala]

Folks,

I need to filter data based on the value of H3 which contains of Month & Year, for example Jan 2014. And the data to filter consist of :
18 May 2014
7 Jan 2014
22 Jan 2014
11 Mar 2014

I just can't figure it out how to filter the data only based on Month & Year, this code below is not working.
Need you help to fix the bug.

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("B16:Z17").AutoFilter
    If Not Intersect(Target, Range("H3")) Is Nothing Then
    Range("B16:Z16").AutoFilter Field:=7, Criteria1:=Range("H3").Value
    End If

regards

 

[dmt32]

Hi,</SPAN>
Providing the value you have in H3 is recognisable as a valid date (e.g. 01/01/2014) but formatted to display MM YYYY then following should work for you.</SPAN>

</SPAN>
 
Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
    Dim ldateto As Long</SPAN>
    Dim ldatefrom As Long</SPAN>
    Dim LastRow As Long</SPAN>
    Dim ThisMonth As Integer</SPAN>
    Dim ThisYear As Long</SPAN>
 
    ThisMonth = Month(Me.Range("H3"))</SPAN>
    ThisYear = Year(Me.Range("H3"))</SPAN>
 
    ldatefrom = DateSerial(ThisYear, ThisMonth, 1)</SPAN>
    ldateto = DateSerial(ThisYear, ThisMonth + 1, 0)</SPAN>
 
    With Me</SPAN>
        .Range("B16").AutoFilter</SPAN>
        LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row</SPAN>
        With .Range("$B$16:$Z$" & LastRow)</SPAN>
            .AutoFilter Field:=7, _</SPAN>
                        Criteria1:=">=" & ldatefrom, _</SPAN>
                        Operator:=xlAnd, _</SPAN>
                        Criteria2:="<=" & ldateto</SPAN>
        End With</SPAN>
    End With</SPAN>
End Sub</SPAN>

Dave</SPAN>

 

[primala]

Hi dmt,

Thanks for quick response, it works like a charm!. Thanks so much.
But I'm still wonder what the does the meaning of these syntax below :

ldatefrom = DateSerial(ThisYear, ThisMonth, 1)</SPAN>
ldateto = DateSerial(ThisYear, ThisMonth + 1, 0)


regards</SPAN>

 

[dmt32]

Hi,
Code changes your Date to a long value by using DateSerial. So date 01/01/2014 changes to: 41640

the next line does the same except it increases month by +1 but with 0 day value you get the last day of required month (not the start on next mont) which in this example will be 31/01/2014 changed to 41670. The filter then tests between these two ranges to filter data which is the only way I know of t make autofilter work with dates.

Dave

 

[primala]

Thank you Dave ...