Delete rows based on date criteria
 

I have a worksheet named PVI_Info, it contains columns A - F. They all have
data in them. Column C has a date and I want a macro to look at the
worksheet and delete all rows that have a date ocurring before 03/22/08.
Then resort based on colum C oldest to newest.
I am usring 2007 Excel - any suggestions...

I have been looking but havent found anything to help yet.

Delete rows based on date criteria
 

Hi,

Right click your sheet tab, view code and paste this and run it.

Sub copyit()
Dim Mydate As Date
Mydate = "22/3/2008"
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & lastrow)
For Each c In MyRange
If c.Value < Mydate Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A1:F" & lastrow).Sort Key1:=Range("C1"), Order1:=xlAscending
End Sub


Mike

"Rookie_User" wrote:

I have a worksheet named PVI_Info, it contains columns A - F. They all have
data in them. Column C has a date and I want a macro to look at the
worksheet and delete all rows that have a date ocurring before 03/22/08.
Then resort based on colum C oldest to newest.
I am usring 2007 Excel - any suggestions...

I have been looking but havent found anything to help yet.