Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows with a macro based on a date value
I have a spreadsheet with 8000 rows. In column E I have dates (estimated
completion dates) - I would like to delete rows that are are not due within the next seven days and/or which are not overdue. eg. Today - 7 September 2009 - I would like to leave all rows that are due upto the 14 September 2009 (left in the spreadsheet would be any outstanding records that are overdue eg. 10 August 2009. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows with a macro based on a date value
Try the below macro which works on the active sheet
Sub MyMacro() Dim lngRow As Long For lngRow = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1 If Range("E" & lngRow) Date + 7 Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Max2073" wrote: I have a spreadsheet with 8000 rows. In column E I have dates (estimated completion dates) - I would like to delete rows that are are not due within the next seven days and/or which are not overdue. eg. Today - 7 September 2009 - I would like to leave all rows that are due upto the 14 September 2009 (left in the spreadsheet would be any outstanding records that are overdue eg. 10 August 2009. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting Rows with a macro based on a date value
Thank you for your assistance - works great just what I was after.
Is it possible to get some more help from you: Column D in my spreadsheet contains either a Date value or the word "NULL". I would like to move all rows with a date value in Column D to Sheet 2 by macro. The original row should be removed from Sheet1. "Jacob Skaria" wrote: Try the below macro which works on the active sheet Sub MyMacro() Dim lngRow As Long For lngRow = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1 If Range("E" & lngRow) Date + 7 Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Max2073" wrote: I have a spreadsheet with 8000 rows. In column E I have dates (estimated completion dates) - I would like to delete rows that are are not due within the next seven days and/or which are not overdue. eg. Today - 7 September 2009 - I would like to leave all rows that are due upto the 14 September 2009 (left in the spreadsheet would be any outstanding records that are overdue eg. 10 August 2009. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows based on a Value | Excel Worksheet Functions | |||
Macro to delete rows based on date | Excel Discussion (Misc queries) | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting rows based on IF statement | Excel Discussion (Misc queries) | |||
Deleting rows based on a cell value | Excel Worksheet Functions |