PDA

View Full Version : Solved: Find a cell with date<actual and delete row.



ermis1975
02-15-2009, 03:05 AM
I want to delete all rows that the date is < of today. All dates are in a specific column. Thanks.

mdmackillop
02-15-2009, 03:55 AM
Sub dates()
Dim Col As String, i as Long
Col = "G"
For i = Cells(Rows.Count, Col).End(xlUp).Row To 1 Step -1
If Cells(i, Col) < Date Then
Cells(i, Col).EntireRow.Delete
End If
Next
End Sub

ermis1975
02-15-2009, 04:18 AM
THANKS!
but I dont want to delete the raw if it has an empty date. (because I have titles in uper left corner).

I replace For i = Cells(Rows.Count, Col).End(xlUp).Row To 5 Step -1

It starts deleting from row 5. But if a date is missing can it skip and dont delete the row?

mdmackillop
02-15-2009, 05:19 AM
BTW How many rows are you processing?

Sub dates()
Dim Col As String, i As Long
Col = "G"
For i = Cells(Rows.Count, Col).End(xlUp).Row To 5 Step -1
If Cells(i, Col) <> "" Then
If Cells(i, Col) < Date Then
Cells(i, Col).EntireRow.Delete
End If
End If
Next
End Sub

ermis1975
02-15-2009, 11:18 AM
It has a lot of rows..30.000 and more.
Something more..
I want to delete (another problem) all raws that the dates in Column B adding to them5 days are < today. (date+5<today)

mdmackillop
02-15-2009, 12:33 PM
Can you post a workbook showing the first 12 or so lines of the sheet to be processed?

ermis1975
02-15-2009, 12:37 PM
example: I have the date 01/01/2009 (in a cell). If I add to that date 5 days and it is < of today I want to delete all raw.

(the first code its ok!).

mdmackillop
02-15-2009, 12:40 PM
The code I showed is inefficient with 30k rows. A filter will be much quicker. Seeing the layout will determine the best way to achieve this.

ermis1975
02-15-2009, 12:58 PM
I dont have all the layout at home. Thats why I cant post it. Now I do that job with filters but without macro.
Is it possible to tell me the code for my second question?..

mdmackillop
02-15-2009, 02:33 PM
Sub dates2()
Dim Col As String, i As Long
Col = "B"
For i = Cells(Rows.Count, Col).End(xlUp).Row To 5 Step -1
If Cells(i, Col) <> "" Then
If Cells(i, Col) + 5 < Date Then
Cells(i, Col).EntireRow.Delete
End If
End If
Next
End Sub