1 Attachment(s)
Delete Filtered Result, Sort From Specific Row And Delete Entire Row Having No Pairs
Row 1 is the header so, it should not be deleted
Data Range starts from A2 and across column O, and rows could vary from 25000+
Now Macro should perform as under
1) First delete all rows where cell in Column F is numeric 0 (Zero), so I write code as under (using filter)
Code:
Selection.AutoFilter
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6, Criteria1:="0"
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
2) Read DATE Data from Cell C2, and if similar DATE is not found in subsequent rows till end (which could run down to some 25000+ rows), delete such entire row. So, All cells in Column 2 should be equal to date in Cell C2, if not delete entire row.
3) Now Cells in 5th Column i.e Column E , starting from Cell E2 contains 3 type of text data, either 'XX', 'YY', or 'ZZ'.
4) So, macro should start sorting only from rows where text 'XX' in column E ends, and for rest of the rows(Range A:O) below it should be sorted in following order
Code:
Selection.Sort Key1:=Range("B"), Order1:=xlAscending, Key2:=Range("D") _
, Order2:=xlAscending, Key3:=Range("E"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
In above code, Range("B"), Range("D"), Range("E"), I guess should be supplemented with row number, which I don't know how to code, and where Data in Column E 'XX' ends.
5) Once above is being done, Data would look like, starting from A2, all data having 'XX' in Column E. Once this 'XX' in column E Ends, due to Sort function being done in 4 above, rest of the data would be like a pair, based on Column B, Column D, Column E, showing data in each rows in Column E having 'YY', followed in subsequent row by 'ZZ'.
6) Now comparison should begin from row (where data in Column E having text data 'XX' ends), and has pair of data 'YY', followed by'ZZ' in following rows, one below other.
7) So, based on Sorting on Clumn B,D,E, if pair of YY, and ZZ is not made, then such data should be deleted.
