Jonathan,
Dates have been nightmare in excel. I have played around with different code and the problem is the date in J1 or P4. I have hard coded that is
ActiveSheet.Range("$I$1:$J$433").AutoFilter Field:=2, Criteria1:="<=02-Aug-2013"
It works, but as soon as I change the criterion to a range, it comes with some funny result. I have tried formatting and changing the data in to date format, but to no avail. When I formatted the P4 or J1 in to number format, it works.
I do not know why 41488.00 works, but (02-aug-13) does not work.
This code works with the date is formatted as number.
Sub delete_rows15()
Dim ms As Worksheet
Set ms = Sheets("Dashboard")
Application.ScreenUpdating = 0
With Sheets("Example")
.AutoFilterMode = False
With .Range("J2:J" & .Cells(Rows.Count, "J").End(xlUp).Row)
'.AutoFilter 1, "<" & .Range("J1")
.AutoFilter 1, "<=" & ms.Range("P4")
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
'.Resize(.Rows.Count - 1).Offset(1).EntireRow.Copy
'ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
End If
.AutoFilter
End With
End With
Application.ScreenUpdating = 0
End Sub
Bookmarks