Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows based on a Value
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows based on a Value
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 -- Gary''s Student - gsnu200860 "Max2073" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows based on a Value
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 -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125349 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows based on values in a a cell in the row | New Users to Excel | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting rows based on number of characters | Excel Discussion (Misc queries) | |||
Deleting rows based on IF statement | Excel Discussion (Misc queries) | |||
Deleting rows based on a cell value | Excel Worksheet Functions |