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

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.

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

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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.

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
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.

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

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" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
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.

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

Send me your test file private and I look at it


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
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.

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

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

"Ron de Bruin" wrote:

Send me your test file private and I look at it


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
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.

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"hindu cliparts" wrote in message
...
I am looking for the same macro. Ron can you please post corrected macro ?
appreciate the reponse. thanks.

"Ron de Bruin" wrote:

Send me your test file private and I look at it


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message ...
Thank you very much Ron!

How would all this work with an inputbox?

Best regards,

Lpdarspe

"Ron de Bruin" wrote:

Oops

xlOr

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
Hello Ron,

I would like to have a macro.

Regards,

LPDARSPE

"Ron de Bruin" wrote:

Hi lpdarspe

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

Or do you want a macro ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lpdarspe" wrote in message
...
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.