Delete rows based on date criteria

[Rookie_User]

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.

 

[Mike H]

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