Filtering Month & Year Excel VBA

primala

New Member
Joined
May 31, 2012
Messages
39
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.


Code:
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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>

Code:
</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>
 
Last edited:

primala

New Member
Joined
May 31, 2012
Messages
39
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

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