Hello,
I’m sorting column F in descending order to push all cells with a valid date to the top of the column. I have F11-F14 hidden (conditional format) since a date does not exist yet. Here is the sort code
Is it possible to add if statements during the sort to exclude cells with the date 1/0/00 so I can sort in ascending order?
Then, I want to run another sort using the range A1:F10 automatically (sorting columns, A, B & C in ascending order). The range would be defined A1 to the last used cell (with a valid date) in column F. The ending cell of column F would change as more dates are added. I tried using MATCH (9.99999999999999E+307, F:F)-CELL("Row",F1)+1 to identify the last used cell in column F but it includes the cells that are hidden with the date format 1/0/00.
Any help is appreciated.
Here is a sample of the worksheet.
F11=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))
I’m sorting column F in descending order to push all cells with a valid date to the top of the column. I have F11-F14 hidden (conditional format) since a date does not exist yet. Here is the sort code
Code:
Sub Ndateq1()
Sheets("Northern").Select
Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End SubIs it possible to add if statements during the sort to exclude cells with the date 1/0/00 so I can sort in ascending order?
Then, I want to run another sort using the range A1:F10 automatically (sorting columns, A, B & C in ascending order). The range would be defined A1 to the last used cell (with a valid date) in column F. The ending cell of column F would change as more dates are added. I tried using MATCH (9.99999999999999E+307, F:F)-CELL("Row",F1)+1 to identify the last used cell in column F but it includes the cells that are hidden with the date format 1/0/00.
Any help is appreciated.
Here is a sample of the worksheet.
| Copy of Master Audit Summary Tracker.xls | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | NYMETRO | 1267 | FARMINGDALE(RELO) | FARMINGDALE | NY | 7/26/06 | ||
| 2 | CENTRAL | 1969 | MCHENRY | MCHENRY | IL | 7/26/06 | ||
| 3 | NYMETRO | 6217 | WALLINGFORD | WALLINGFORD | CT | 7/21/06 | ||
| 4 | CENTRAL | 1920 | CRYSTALLAKE | CRYSTALLAKE | IL | 7/19/06 | ||
| 5 | OHIOVALLEY | 3814 | CROSSCOUNTY(COLERAIN) | CINCINNATI | OH | 7/19/06 | ||
| 6 | OHIOVALLEY | 3832 | PLEASANTRIDGE | CINCINNATI | OH | 7/12/06 | ||
| 7 | NJMETRO | 974 | MANTUA | MANTUA | NJ | 5/17/06 | ||
| 8 | OHIOVALLEY | 3817 | STRONGSVILLE | STRONGSVILLE | OH | 5/17/06 | ||
| 9 | CENTRAL | 2030 | VALPARAISO | VALPARAISO | IN | 5/11/06 | ||
| 10 | NJMETRO | 1286 | HAMBURG | HAMBURG | NY | 5/10/06 | ||
| 11 | NYMETRO | 901 | EHANOVER | EASTHANOVER | NJ | 1/0/00 | ||
| 12 | NJMETRO | 902 | LAKEWOOD | LAKEWOOD | NJ | 1/0/00 | ||
| 13 | NJMETRO | 903 | SPLAINFIELD | SOUTHPLAINFIELD | NJ | 1/0/00 | ||
| 14 | NYMETRO | 904 | PARAMUS | PARAMUS | NJ | 1/0/00 | ||
Northern | ||||||||
F11=IF(ISERROR(INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))," ",INDEX(Master!$J$2:$J$2048,MATCH(B11,Master!$D$2:$D$2048,0)))
