Solved Need help using date as criteria in vba excel sheet

July 13, 2016 at 17:14:35
Specs: Windows 7
I have a sheet that I track my loan closings. I am trying to write a macro that will show me all closings for the current month we are in. Column 13 is the column that has a closing date for every loan, I don't know and can't find the code that tells excel to show only loans that are the same month as the current month we are in (I am also filtering out pre-approvals); I need to know what to put where the question marks are:

This is what I have:
Sub PipelineShowThisMonth()
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
End If
ActiveSheet.Range("$A$5:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=13, Criteria1:="???????"
End Sub

message edited by mecerrato

See More: Need help using date as criteria in vba excel sheet

Reply ↓  Report •

July 13, 2016 at 19:11:34
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.

As for your question, my first thought was to use a Helper Column in your sheet and then filter on the Helper Column.

e.g. Put =MONTH(M6) in an empty column and drag it down. I used 12, but it doesn't matter. You can even hide the column if you don't want to see it.

Then your code would look like this:

ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=12, Criteria1:=Month(Date)

Since the Helper Column would always contain the current month number for Dates within the current Month, those numbers would be a match for the result of Month(Date) within VBA.

Then I thought about it for a second longer and decided that you can't be the first one to want to filter on the current month. I used my excellent Googling skills and found the following at:

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

There's no need for a Helper Column when that method is used. What is it doing is looking for dates that are >= to first of this month and <= the day before the first day of next month. In other words, any date within the current month.

Assuming you choose the VBA-only method, please keep the concept of a Helper Column in mind. There are many times when a intermediate step is required to get to the end result. This applies both within Excel and also when using VBA. That intermediate step can be placed in a Helper Column and referred to for further calculations.

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

Reply ↓  Report •

July 14, 2016 at 00:46:16
This however was a lot of superfluous code. The easiest and best way is as shown below;

Sub ApplyAutoFilters()

With ActiveSheet

.AutoFilterMode = False


End With

End Sub
In the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.
Read more:

Reply ↓  Report •

July 14, 2016 at 06:59:35
BTW...your filter for "<>Pre-Approval" starts in Row 5 but your filter for the Date starts in Row 6.

Was that intentional? Seems like they should be consistent.

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

Reply ↓  Report •

Related Solutions

July 14, 2016 at 08:28:05
thanks all I used the code from DerbyDad and it did the trick, the rows where wrong thanks for that.

Reply ↓  Report •

Ask Question