I want to delete all rows that the date is < of today. All dates are in a specific column. Thanks.
I want to delete all rows that the date is < of today. All dates are in a specific column. Thanks.
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?
BTW How many rows are you processing?
[VBA]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[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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)
Last edited by ermis1975; 02-15-2009 at 11:28 AM.
Can you post a workbook showing the first 12 or so lines of the sheet to be processed?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!).
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?..
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote