Advance Filter: Filter current month date range?

Glitch5618

Board Regular
Joined
Nov 6, 2015
Messages
105
Using excel 2007.

I have what may be a simple question with a simple answer. I can only hope..

What I'm trying to do is filter a large list of data by the current month. I'm aware that for AUTOFILTER its a simple bit of code like the following:
Rich (BB code):
'Filter for current month
With dWS
    .AutoFilterMode = False
    With fRng
        .AutoFilter Col, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
        On Error Resume Next
    End With
End With
However the issue is I'm not using an autofilter. I have a large data set and I need to be able to do the same thing as the code above but with my advanced filter code below:

Rich (BB code):
'If date range is entered for outlier data
If dVer = True And f.chkOutlier.Value = True Then
    GenOutlierData
    Application.ScreenUpdating = False
End If


'Copy Column Headers from data sheet to filter sheet
dWS.Activate
LC = dWS.Cells(1, Columns.Count).End(xlToLeft).Column
Set dRng = dWS.Range(Cells(1, 1), Cells(1, LC)) 'Find last column within escalation data
dRng.Copy Destination:=fWS.Range("A2")
fWS.Cells.ClearFormats


'Create Criteria Range Headers, MUST match escalation data headers exactly
fWS.Range("AA3").Value = "Agent Name"
fWS.Range("AB3").Value = "Manager Name"
fWS.Range("AC3").Value = "LOB"
fWS.Range("AD3").Value = "BAM"
fWS.Range("AE3").Value = "Site"
fWS.Range("AF3").Value = "Nest/Prod" 'Nesting/Production
fWS.Range("AG3").Value = "Date" 'Start Date
fWS.Range("AH3").Value = "Date" 'End Date


'Set CopyToRange for Advanced fitler
fWS.Activate '''fWS sheet MUST be active before advanced filter is ran for it to work!
Set fRng = fWS.Range(Cells(2, 1), Cells(2, LC))


'Advanced filter
On Error GoTo EndCode
dWS.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("AA3:AH4"), copytorange:=fRng, Unique:=False

As it stands right now my code is rather complicated to do what I think should be a simple few lines of code (I have not included all the code). Right now I have to apply an autofilter for the current month to the dataset, copy the results to a temp worksheet, and then apply my advanced filter.

I imagine what one would need to do is to have the system put the current date in cell AH3 (end date) and somehow have the program return the first day of whatever current month it is and convert that into a date placed in cell AG3 (start date). I'm just at a loss on how to do that. I would love it if someone could help me with this issue. It would cut down on my programs run time tremendously. Thanks in advance for anyone who takes the time to help me with this issue.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Have you considered a helper column, that just generates month and year i.e =TEXT(B9,"mm yy") or similar

actually in AG3 =TEXT(AH3,"mm yy")
 
Last edited:

Glitch5618

Board Regular
Joined
Nov 6, 2015
Messages
105
Well the data set I'm filtering already has a date column in the format of MM/DD/YYYY

I'm not sure how that would give me the intended results though.
 
Last edited:

Glitch5618

Board Regular
Joined
Nov 6, 2015
Messages
105
It appears I've found an answer to my question myself. For anyone who may run into this issue here is the solution.

Rich (BB code):
'Create Criteria Range Headers, MUST match escalation data headers exactly
fWS.Range("AA3").Value = "Agent Name"
fWS.Range("AB3").Value = "Manager Name"
fWS.Range("AC3").Value = "LOB"
fWS.Range("AD3").Value = "BAM"
fWS.Range("AE3").Value = "Site"
fWS.Range("AF3").Value = "Nest/Prod" 'Nesting/Production
fWS.Range("AG3").Value = "Date" 'Start Date
fWS.Range("AH3").Value = "Date" 'End Date


'If dver is false then code will filter for current month
If dVer = False Then
    fWS.Range("AG4").Value = ">=" & Month(Date) & "/01/" & Year(Date) 'Start Date
    fWS.Range("AH4").Value = "<=" & Date 'End Date
End If

Just to explain, dVer is a boolean that returns true if a valid date has been entered in my userform, or false if not. With this code and today's date the code will return >=3/01/2016 for cell AG4 and <=3/22/2016 for cell AH4 which for the purpose of my advanced filter is exactly what I needed. Now if no date is entered by a user they will only see the current month to date. Hope this helps someone!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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