delete based on date in input box
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "I" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim myDate

With ActiveSheet

myDate = InputBox("Input date")
If Not IsDate(myDate) Then
MsgBox "Invalid date"
Exit Sub
End If
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value < myDate Then
.Rows(i).Delete
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Light" wrote in message
...
I have written a macro to delete the row if cell I < 19.12.2006

I have two problems. To get my macro to work, I've had to use the
numerical
value for 19th Dec 39070 and I'd much prefer to use 19.12.2006 or similar.

The second problem is that this date will change, so next time I run the
macro I might want the date 30.12.2006. Is there a way of getting a pop-up
box to enter my date in and the macro using this date?

I'd be very grateful if anyone could point me in the right direction
Thanks!