Code:
Sub delete_cancelled()
Sheets("IE").Select
Application.ScreenUpdating = False
finalrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To finalrow
For j = 2 To finalrow
'establish blotter, account number, etc.
blotter = Range("p" & i)
account = Range("j" & i)
net_amt = Abs(Range("e" & i))
amt = Abs(Range("a" & i))
buy_sold_indic = Range("l" & i)
sd = Range("x" & i)
Tag = Range("aa" & i)
cancel_indic = Range("o" & i)
'establish botter, account number, etc. for non-cancelled trades
blotter2 = Range("p" & j)
account2 = Range("j" & j)
net_amt2 = Abs(Range("e" & j))
amt2 = Abs(Range("a" & j))
buy_sold_indic2 = Range("l" & j)
sd2 = Range("x" & j)
tag2 = Range("aa" & j)
cancel_indic2 = Range("o" & j)
'if blotter, account, etc. for the cancelled trade equals the blotter, account, etc. for non-cancelled trades, and the cancelled and non-cancelled trades buy/sold and cancel indicators don't equal to each other, then cut the cancelled row and the non-cancelled row to the IE Cancelled tab
If blotter = blotter2 And account = account2 And net_amt = net_amt2 And sd = sd2 And Tag = tag2 And buy_sold_indic <> buy_sold_indic2 And cancel_indic <> cancel_indic2 Then
Range("a" & i).EntireRow.Cut
Sheets("IE Cancelled").Activate
totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("a" & totalrow).Select
ActiveSheet.Paste
Sheets("IE").Activate
Range("a" & j).EntireRow.Cut
Sheets("IE Cancelled").Activate
totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("a" & totalrow).Select
ActiveSheet.Paste
Sheets("IE").Activate
End If
Next j
Next i
'delete blank rows
Sheets("IE").Activate
finalrow = Cells(Rows.Count, "A").End(xlUp).Row
For k = finalrow To 2 Step -1
If Range("a" & k) = "" Then
Range("a" & k).EntireRow.Delete
End If
Next k
essentially the macro is trying to loop through thousands for rows ....and if it finds any pair of offsetting trades, move them to the canceled/offsetting worksheet
offsetting trades are essentially identically , but have opposite values (e.g., 100 buys, other has -100 sells) and a few other categories (sample rows will be provided in the next post).
i thought the code above did the trick, but it didnt seem to work for me this month.
also, it's very slow.....any way to make it more efficient?