Macro to Delete Specific Rows (Condition)

**leviathan86** · 04-06-2009, 06:11 AM

I need help with a macro do to the following:

If Row 1 = "Cancel" then delete previous row. If Row 1= "Cancel" then delete Row 1. I am not sure if that is logical, but what needs to happen is:

Focus on a row, if the previous row contains Cancel then delete focused row. Then delete the row which has cancel.

So add new/cancel rows will be deleted (in red)

1 USD 1.2458 FXL 400863 ADD NEW
2 CAD 1.2453 FXL 400262 ADD NEW
3 PKR 80.55 REU 400160 ADD NEW
4 PKR 80.55 REU 400144 ADD NEW
5 PKR 80.55 REU 400160 CANCEL
6 PKR 80.55 REU 400153 REINSTATE
7 USD 1.4211 PHO 401151 ADD NEW
8 PKR 79.45 PHO 100559 ADD NEW
9 USD 1.32 REU 401151 ADD NEW
10 USD 1.3206 FXL 400262 ADD NEW
11 PKR 80.46 PHO 201323 ADD NEW
12 PKR 80.46 PHO 201323 CANCEL
13 PKR 80.46 PHO 201323 REINSTATE

**DonkeyOte** · 04-06-2009, 06:16 AM

One way

Public Sub DeleteCancel()
Dim lngRowi As Long
For lngRowi = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    If UCase(Cells(lngRowi, "F")) = "CANCEL" Then Rows(Application.Max(lngRowi - 1, 1) & ":" & lngRowi).EntireRow.Delete
Next lngRowi
End Sub

Alter Ranges to suit requirements