Filter and delete rows based on date prior to Current Month

[Pat_The_Bat]

Trying to delete any rows that have a date prior to the 1st day of the current month. When I debug.print my variable MTD it gives the date 8/1/2018, but then when I filter and delete rows it goes haywire. Any advice?

Sub MTDapps()
With Sheets("Table")
    Rows.Sort Range("J2"), order1:=xlDescending, Header:=xlYes
    End With
    
Dim MySheet As Worksheet
Dim LastRow As Long, LastCol As Long
Dim MyRange As Range


Application.DisplayAlerts = False


Dim m As Integer
Dim y As Integer
Dim MTD As Date


m = month(Now)
y = year(Now)


MTD = m & "/1/" & y


Debug.Print m
Debug.Print y
Debug.Print MTD




Set MySheet = ThisWorkbook.Worksheets("Table")




With MySheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
    
    Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With


With MyRange
    .AutoFilter Field:=1, Criteria1:="<" & MTD
    
       
    


    '.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With




    


End Sub

 

[steve the fish]

You can use DateSerial function.

myDate = DateSerial(Year(Date), Month(Date), 1)

 

[Pat_The_Bat]

You can use DateSerial function.

myDate = DateSerial(Year(Date), Month(Date), 1)

Same result. Were you indicating that the way I've established the value for MTD is the problem? Whether I set MTD as the control date by

MTD = DateSerial(year(Date), month(Date), 1)

or with

MTD= m & "/1/" & y

either way the variable is set to 8/1/2018 but I still don't get the correct result from the macro. The spreadsheet basically appears to have a filter and no data showing. It's not filtering out any row where the date in column J is prior to 8/1/2018 as expected.

 

[steve the fish]

Ok lets try converting the date to an integer. If you have dimensioned the variable change that to Long. See if this filters correctly:

myDate = CLng(DateSerial(Year(Date), Month(Date), 1))

 

[Ray Bailey]

Hi, This should work. (Did for me)

sDate = "<" & Format(Date - Day(Now) + 1, "mm/dd/yyyy")

With MyRange
.AutoFilter Field:=1, Criteria1:=sDate


Also, out of interest, is there a reason why you use a range and not ActiveSheet.UsedRange.AutoFilter?

I'm still getting to grips with the best way of doing things.


Cheers

Ray