Delete rows based on date criteria

  • Thread starter Thread starter Rookie_User
  • Start date Start date
R

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.
 
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
 
Back
Top