Delete Cells By Searching For Date
Posted by No Clue on September 19, 2001 3:45 AM
I've got a problem! I've got in worksheets "Sheet1" all data from column 1 til column 26! In "Sheet2" cell "G10" and "G12" I wanna be able to fill in a date and that it will search in column 3 on Sheet1 in which all the dates are! If it finds a match with the dates or dates in between the dates filled in G10 and G12 that it will delete those dates in Sheet1 and that if for example it finds a date that matches it will delete all the data in that row from column 1 til column 26! For example if I have in Sheet2 G10 as date "9-01-01" and in G12 "9-20-01" that it will delete all the rows from column 1 til column 26 which contains a date that's between the dates in Sheet2 cells G10 and G12! And when that is done that it gives a message which says "Delete is completed"! Hope someone can help me? Thanks!
Posted by Eric on September 19, 2001 6:00 AM
If someone could show me how to do this with an advanced filter I'd appreciate it. Until then...
If your date values are in C2:C27, paste the following formula in D2 and copy down to D27
=(C2<Sheet2!G$10)=(C2<Sheet2!G$12)
This will give the value "false" if the date falls between the dates you set in sheet2!g10 and g12.
Then type "filter" in D1, select cell D1, go Data-->Filter-->autofilter
You'll see an inverted triangle beside each cell in row 1. Click on inverted triangle in D1, you'll get a drop-down list, choose "true". Now only rows in which col (D) are "true" get displayed. If you then go back and change values in g10 and g12, go back to sheet1!D1 and reselect the "true" option- it will refilter list. If you copy and paste the filtered data, it will only paste the data that passed the filter.
You can go back and select "all" as the filter to get your original data. You can also go back to Data-->Filter and Unselect autofilter to get rid of it altogether.
There are definitely better ideas out there- watch for more posts!
