Delete Filtered Result, Sort From Specific Row And Delete Entire Row Having No Pairs

[analyst]

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)

    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

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.

[Excel Fox]

Are you looking to get this solved, or are you posting a solution?

[analyst]

Sir,
I've posted part of the solution, what I was knowing through learning from the Forum.

I am looking to solve this, kindly give the solution to this query. Thanks

[analyst]

Anybody looking at this query, or should I modify?

[p45cal]

This is just a start, and not terribly robust, but the following gets you up to and including point 5 assuming you have an un-autofiltered sheet to start with:

Sub blah()
Dim yyy As Range
mydate = CLng(Range("C2")) 'grabbed now in case row 2 gets deleted whilst filtering.
Range("A1").AutoFilter Field:=5
Set xxx = ActiveSheet.AutoFilter.Range
Set zzz = xxx.Offset(1).Resize(xxx.Rows.Count - 1)
With xxx
  .AutoFilter Field:=6, Criteria1:="0"
  On Error Resume Next
  Set yyy = zzz.SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  If Not yyy Is Nothing Then yyy.EntireRow.Delete
  .AutoFilter Field:=6
  .AutoFilter Field:=3, Criteria1:="<>" & mydate
  On Error Resume Next
  Set yyy = zzz.SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  If Not yyy Is Nothing Then yyy.EntireRow.Delete
  .AutoFilter Field:=3
  .AutoFilter Field:=5, Criteria1:="<>XX"
  On Error Resume Next
  Set yyy = zzz.SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  If Not yyy Is Nothing Then
    StartRow = yyy.Row
    yyy.Sort Header:=xlNo, Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("D1"), Order2:=xlAscending, Key3:=Range("E1"), Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    .AutoFilter Field:=5
  End If
End With
End Sub

The problem now is that because you have redacted data in column B the yys and zzs do not end up in pairs. I have however put the row number of the topmost row after the XX rows in StartRow.

[analyst]

@p45Cal

Thanks Brother, the above code works, except what is sought at Serial No. 5,6,7 in post #1, however is not done properly. I mean where there is no pair of row having data in Column E "YY", followed (subsequent row) having "ZZ" is not deleted.

[p45cal]

@p45Cal

Thanks Brother, the above code works, except what is sought at Serial No. 5,6,7 in post #1, however is not done properly. I mean where there is no pair of row having data in Column E "YY", followed (subsequent row) having "ZZ" is not deleted.

I said as much in my last post. Since you have redacted data from the sheet your sample file does not appear in pairs below the last XX. I was hoping you'd post another file with more realistic data, still redacted but more 'true-to-life'. You can do this with search and replace used with a bit more finesse so that at least column B isn't all the same. Ideally, all the sort columns would contain sortable data. I know it's a bit of work for you but it means less work for me and I won't be guessing wrongly about the kind of data you have in your sheet. Help me to help you.