I need to filter and delete rows of information between 2 dates

[Guest]

I have a spreadsheet with 12 columns of data and one column has dates. I
need a macro to delete out rows of information that are less than date1 and
greater than date2. Ihis will leave information that is greater than or
equal to date1 and less than or equal to date2. In the macro I need ask for
and get date1 and date2 for input from the user.

I tried recording the steps and inputing the date1 and date2 in certain
cells prior to this, but it did not duplicate it when I ran it again.

 

[Ron de Bruin]

Hi lpdarspe

You can use EasyFilter to do this
http://www.rondebruin.nl/easyfilter.htm

Or do you want a macro ?

 

[Guest]

Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

 

[Ron de Bruin]

This is a small example

Do you use a userform to ask the user for the dates or do you want to use a inputbox ?
Remember that there must be a good error check to see if the user enter a real date

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range

DeleteValue1 = "<=1/10/2006"
DeleteValue2 = ">=1/25/2006"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue1, _
Operator:=xlAnd, Criteria2:=DeleteValue2
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

 

[Ron de Bruin]

Oops

xlOr

 

[Guest]

Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

 

[Ron de Bruin]

Try this

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range

DeleteValue1 = InputBox("First date")
DeleteValue2 = InputBox("Second date")
If IsDate(DeleteValue1) And IsDate(DeleteValue2) Then
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:="<=" & DeleteValue1, _
Operator:=xlOr, Criteria2:=">=" & DeleteValue2
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Else
MsgBox "Enter a correct date in the inputbox"
End If
End Sub

 

[Guest]

Hello Ron,

It did not work. It deleted all my data rows with dates. This is my first
real attempt to do any kind of programming like this.

I am not sure what went wrong. In the case I am trying to work I have
column N with dates. The rows in column N can vary widely. As I said I want
to delete rows with dates in column N that are less than date1 or greater
than date2.

What does the .Range(A1:A100)? Can I change that to Columns(N:N) to fit my
case?

Thanks for the help.

Lpdarspe

 

[Ron de Bruin]

What does the .Range(A1:A100)? Can I change that
Yes change that to the range in N

 

[Guest]

Ron,

I made the changes and it still deletes all the rows with dates in column N.
I do not know enough about the programming codes to try to figure out what
is wrong.

 

[Ron de Bruin]

Send me your test file private and I look at it

 

[Guest]

I am looking for the same macro. Ron can you please post corrected macro ?
appreciate the reponse. thanks.

 

[Ron de Bruin]

Hi hindu cliparts

Are you on a US machine ?
Do you want to use a inputbox ?

You can also use EasyFilter for this (more options)
http://www.rondebruin.nl/easyfilter.htm