CODEWITHSUNDEEP
vbaexcel
user9351236
user9351236

Reputation: 143

Applying filter for date less than equal to last day of the current month not working

I am extracting the first day and last day of previous month, and applying the filter to a date column where the records between those dates should be displayed.

 first_dy = DateSerial(Year(Date), Month(Date) - 1, 1)
   last_dy = DateSerial(Year(Date), Month(Date), 0)
   With Worksheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1:R100000").Select
        Selection.AutoFilter

   Worksheets("Sheet1").Range("A1").AutoFilter Field:=15, Criteria1:="<" & last_dy, Operator:=xlAnd, Criteria2:=">" & first_dy

The above code is displaying zero records. But, when I am applying only the criteria for greater than first day of previous month, its showing me the correct records of that month and the current month. Below code for reference:

  Worksheets("Sheet1").Range("A1").AutoFilter Field:=15, Criteria1:=">=" & first_dy

But, when I am applying the filter for the records greater than the last day of previous month, its not showing me any record. The code is working fine if I am using the below code. It is showing me records only of the last day of previous month:

Worksheets("Sheet1").Range("A1").AutoFilter Field:=15, Criteria1:="=" & last_dy

Upvotes: 1

Views: 1484

Answers (1)

DisplayName
DisplayName

Reputation: 13386

use CLng()

Dim first_dy As Variant, last_dy As Variant

first_dy = DateSerial(Year(Date), Month(Date) - 1, 1)
last_dy = DateSerial(Year(Date), Month(Date), 0)
With Worksheets("Sheet1")
    .Range("A1").AutoFilter Field:=15, Criteria1:="<" & CLng(last_dy), Operator:=xlAnd, Criteria2:=">" & CLng(first_dy)
End With

Upvotes: 1

Related Questions