Fancy Formula Forthcoming?

[mikemcbain]

G'day Magicians

I require a fancy formula to include in a macro?
I wish to Delete all Rows where both Column B (Track) and Column C (RN) are identical AND Delete all Rows where Column G (SB1W) is Greater than 4.1
In the example below it would just leave the row containing 10/05/2018 TOWNSVILLE 2 3 12x2 One Bar None 3.3

With thanks in great anticipation

Mike.

DATE      	  TRACK           	 RN	TN	FORM	HORSE               SB1W
10/05/2018	TOWNSVILLE      	1	6	6551	Craiglea Simmo      	16
10/05/2018	TOWNSVILLE      	2	3	12x2	One Bar None        	3.3
10/05/2018	TOWNSVILLE      	3	14	3402	Lady Mirabella      	14
10/05/2018	TOWNSVILLE      	4	2	3x11	Dalon               	 3
10/05/2018	TOWNSVILLE      	4	7	3341	Craiglea Ella       	20
10/05/2018	TOWNSVILLE      	5	2	0x82	Hidden In Heaven    	3.9
10/05/2018	TOWNSVILLE      	5	8	24x1	Mr Sonador          	8.5
10/05/2018	TOWNSVILLE      	5	13	 8x1	Gabriala            	51
10/05/2018	TOWNSVILLE      	5	14	2581	Will He Torque      	11
10/05/2018	TOWNSVILLE      	6	2	x231	Fine 'n' Devine     	4.6
10/05/2018	TOWNSVILLE      	6	11	701	Sidthekid           	126
10/05/2018	TOWNSVILLE      	7	2	x341	Zap                     2.4
10/05/2018	TOWNSVILLE      	7	6	2251	Choux Bacquat       	6
10/05/2018	TOWNSVILLE      	7	11	4x32	Nadeem Esprit       	23
10/05/2018	PAKENHAM        	2	1	 0x2	Bolghari            	3.4
10/05/2018	PAKENHAM        	2	7	2232	Once Upon A Dream      3.5
10/05/2018	PAKENHAM        	3	6	27x2	Star Clipper        	4.8
10/05/2018	PAKENHAM        	4	4	852	Duke Of Castille    	3.7
10/05/2018	PAKENHAM        	4	7	7232	Savvy Acquisition   	3.7

 

[mikemcbain]

G'day Fluff

The formula you provided is doing exactly what I required and at the risk of frustrating you can I ask for a slight modification in the light of my further experience?

Instead of...
I wish to Delete all Rows where both Column B (Track) and Column C (RN) are identical AND Delete all Rows where Column G (SB1W) is Greater than 4.1

Can I have...
I wish to Delete all Rows where Column A(Date) AND Column B (Track) AND Column C (RN) are identical AND Delete all Rows where Column G (SB1W) is Greater than 4.1

I will understand if you do not wish to carry out this mod but will be very grateful if you will

Best regards

Mike.

 

[Fluff]

How about

Sub DelSomeRows()
   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value
         If Not .exists(ValU) Then
            .Add ValU, Cl
         Else
            If Rng Is Nothing Then Set Rng = Union(.Item(ValU), Cl) Else Set Rng = Union(Rng, .Item(ValU), Cl)
         End If
         If Cl.Offset(, 5).Value > 4.1 Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   Rng.EntireRow.Delete
End Sub

 

[mikemcbain]

Beautiful work Fluff!

Many thanks much appreciated.

Mike
Tasmania.

 

[Fluff]

Glad we could help & thanks for the feedback