VBA Filter on Date

[SAldrich]

Hello everyone,

I have a macro which I want to filter data against a date which the user enters then delete the visible rows however when I run this, it doesn't return anything in the filter so I think it is not recognising the criteria as a date. Any ideas on how to resolve this?

Thanks for your help!!


Sub Delete_old()

Dim CutOffDate As Date

CutOffDate = Application.InputBox("Enter the cut off date in the Format ""DD/MM/YYYY""")

Selection.AutoFilter
Selection.AutoFilter Field:=17, Criteria1:="<=" & CutOffDate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("A1").Select

End Sub

 

[Richard Schollar]

Hi

try this:

Sub Delete_old()

Dim CutOffDate As Long
Dim temp As String

 temp = Application.InputBox("Enter the cut off date in the Format ""DD/MM/YYYY""")
 
CutOffDate = CLng(DateValue(temp))

Selection.AutoFilter
Selection.AutoFilter Field:=17, Criteria1:="<=" & CutOffDate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("A1").Select

End Sub

 

[SAldrich]

Genius!!! Thank you!

 

[Sudarshan_1026]

Hi I am using the below code to filter data between. The issue i am facing is , when data is entered to any cell in the workbook the filters refresh happens. I am looking for teh filter to refresh only when the data in the cell C1 or D1 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim StartDate As Long
Dim EndDate As Long



StartDate = Range("C1").Value
EndDate = Range("D1").Value


Range("A3").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate






End Sub

 

[RPM1979]

Re: VBA Filter on 2 dates entered by user

Hello everyone,

I have a macro which I want to filter data against 2 dates which the user enters. Any ideas on how to resolve this?

User will enter 2 dates in Input Box and macro should filter date basis 2 dates entered. Dates are stored in column D of worksheet "Copy"

I am in urgent need of same so please assist on same quickly.

Thanks for your help in advance!!

 

[pexcel1990]

Re: VBA Filter on 2 dates entered by user

Hi ,

Did you get answer for this.

even i have same problem.

if you resolved can you please share the code as soon as possible.

Thank you.