Deleting Rows based on a Value

[Max2073]

I have a spreadsheet with 50,000 rows. In column 'E' I have dates, I would
like to run a macro that deletes rows that have a date greater then todays.
eg E100 is 30 August 2009 today is the 14 August 2009 row is to be delete.

I also have another spreadsheet about the same size - In column 'D' I have
some dates. I would like to run a macro that deletes rows that have a date.
There are no blank cells in column 'D' it is either a date or has the word
NULL.

 

[Gary''s Student]

Try this small macro:

Sub RowKiller()
Dim n As Long, i As Long, v1 As Date, v2 As Date
n = Cells(Rows.Count, "E").End(xlUp).Row
v1 = Date
Application.ScreenUpdating = False
For i = n To 1 Step -1
v2 = Cells(i, "E").Value
If v2 > v1 Then
Cells(i, "E").EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[p45cal]

Sub blah()
For rw = 50000 To 2 Step -1
If IsDate(Cells(rw, "E").Value) And Cells(rw, "E").Value > Date Then
Rows(rw).Delete
Next rw
End Sub