SEPARATE ROWS WHEN CONDITIONS MET

[pmari]

Hi Friends,

My workbook having Order No. in Column “B” and name of the items in another column say “C” Suppose items are Name of Fruits. I need to separate Orders with MANGO or APPLE or ORANGE or ANY TWO or ALL THREE ITEMS.

Orders with ANY OTHER COMBINATION not to be selected. ( Say APPLE with PAPAYA or MANGO with PINE APPLE)

Sample file attached for details. Please help me out. Any Help wouls Help me to same my time and Highly appreciated.

With Best regards,PM.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim vecFruit As Variant
Dim Lastrow As Long
Dim Endrow As Long
Dim fExc As Boolean
Dim Cus As String
Dim i As Long

Application.ScreenUpdating = False

vecFruit = Array("APPLE", "MANGO", "ORANGE")

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

Endrow = i
Cus = .Cells(i, "A").Value2
fExc = False
Do

If IsError(Application.Match(.Cells(i, "C").Value2, vecFruit, 0)) Then

fExc = True
End If
i = i - 1
Loop Until .Cells(i, "A") <> Cus
i = i + 1

If fExc Then Rows(i).Resize(Endrow - i + 1).Delete
Next i
End With

Application.ScreenUpdating = True
End Sub[/vba]

[pmari]

Dear Sir,

Many Thanks for the code , simply superb.