Solved Autofilter to filter using current month as criteria

August 1, 2016 at 09:29:04
Specs: Windows 7
I have a macro below that filters out certain data; I would like to add an additional filter based on the criteria for the current month. here is the logic: in addition to the other filters already in the macro only show data if column #35 = current month.

Can someone help me with this?

Code:

Sub Pipeline_ShowNetRegs()
'
ActiveSheet.Unprotect
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
ActiveSheet.Range("$a$7:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=8, Criteria1:="<>Post-Closing"
ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=7, Criteria1:="<>DECL", _
        Operator:=xlAnd, Criteria2:="<>WITH"
ActiveSheet.Protect AllowFiltering:=True
End Sub


See More: Autofilter to filter using current month as criteria

Report •

✔ Best Answer
August 2, 2016 at 08:25:26
Try this. This should filter by the current month and also return any rows where Column 32 is blank.

ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=32, _
           Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(1, Now)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
Report •

#2
August 1, 2016 at 15:55:07
DerbyDad, I am not sure what happened here but it did not work and for some reason I marked it as answered, my apologies, I got pulled into a different direction and never revisited the code.

I stepped through the code and it filters everything out when using the Date Serial code.
when using the helper column (Month()DATE)) method I get a different error, Run-time error 1004 "Autofilter method of range class failed"

 Sub DropDown261_Change()
ActiveSheet.Unprotect
Dim mysht As Worksheet
Dim myDropDown As Shape
Dim myVal As String

Set mysht = ThisWorkbook.Worksheets("Pipeline")
Set myDropDown = mysht.Shapes("Drop Down 261")
myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
'
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
ActiveSheet.Range("$a$7:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
'ActiveSheet.Range("$a$7:$AQ$1000").AutoFilter Field:=8, Criteria1:="<>Post-Closing"
ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=1, Criteria1:=myVal
'ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=48, Criteria1:=Month(Date)

ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=32, _
           Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
           Criteria2:="<=" & DateSerial(Year(Date), Month(Date) + 1, 1) - 1

ActiveSheet.Protect AllowFiltering:=True
End Sub


Report •

#3
August 2, 2016 at 03:35:12
DerbyDad after some more testing I realized the code does work but it is only showing data if column 32 is within the current month which is by design and correct. I did not realize that some of the data in the column 32 are blank . So how can I add a criteria 3 that also shows the row of data if column 32 is blank?

Report •

Related Solutions

#4
August 2, 2016 at 08:25:26
✔ Best Answer
Try this. This should filter by the current month and also return any rows where Column 32 is blank.

ActiveSheet.Range("$A$7:$AQ$1000").AutoFilter Field:=32, _
           Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(1, Now)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question