macro to delete rows and sort

I have attached a worksheet to illustrate a "Before" (raw data) and "After" (desired results).

Each row contains data on a forex trade. The variables which must be assessed in each row are:

Currency Pair (e.g. GBP/USD).
Opening Time
Closing Time

What I need to do is delete many rows in the raw data because they reflect a condition I do not want in my analysis, which is having multiple open trades of a single currency pair during the same time period.

To perform my analysis I need to delete all trades (rows) which meet the above condition.

The worksheet provides examples, along with a rudimentary tool I've used to help me sort/delete rows manually.

Since I am looking at thousands of trades, a manual sort/delete is impractical. (my eyes don't like this kind of work!)

The rudimentary tool I've used in manual sorts/deletes is simply to have a column which returns a "1" if a trade opens before an older trade has closed. I do this by first sorting the data by (a) currency pair then (b) date opened. Then I must manually go through multiple iterations of deleting trades until there all of the "1's" have disappeared.

I'm hoping someone can show me a macro which might do this sorting/testing/deleting automatically, at least to the point where all I have to do is repeatedly press a "macro" button until there are no "1's"

Thanks much for any help!

In B15 and copy down,

=SUMPRODUCT( (H$14:H14 = H15) * (J$14:J14 > I15))

Rows with non-zero values should be deleted. You can decide if the > should be >=

The VBA is strightforward, but your example, with the excess narrative, is not likely in the actual layout.

How about posting an actual example, sanitized as necessary?

Sanitized worksheet attached, with broken macro.

Objective is to delete rows in column C which <>0, while retaining integrity of column W.

Macro for which I need help:

Please Login or Register  to view this content.

Thanks!

Do Insert > Name > Define,

rgnFormula refers to: =INDEX(TradeList!$C:$C, ROW(TradeList!$C$14) + 1):INDEX(TradeList!$C:$C, MATCH("zzz", TradeList!$D:$D))

Then run this:

Please Login or Register  to view this content.