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

 

[njimack]

Sub Fancy()
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
        If .Cells(i, 2) = .Cells(i, 3) Or .Cells(i, 7) > 4.1 Then .Cells(i, 1).ClearContents
    Next i
    With Range(.Cells(2, 1), .Cells(lastrow, 7))
        .AutoFilter field:=1, Criteria1:="="
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    .ShowAllData
End With
Application.ScreenUpdating = True
 
End Sub

 

[Fluff]

Another option

Sub DelSomeRows()
   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 1).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

 

[Peter_SSs]

Mike, how big is your actual data set?

 

[mikemcbain]

njimack

Many thanks for your very prompt response with the above code, much appreciated.

However I am having a aproblem with it that I am not capable of fixing sorry.

It is removing the rows where Col G is over 4.1 but it is not removing the rows where both Column B (Track) and Column C (RN) are identical ?

It also comes up with the Debug screen at .ShowAllData ?

Any suggestions to help me?

With thanks

Mike.

 

[mikemcbain]

Peter

Data set varies a little Columns always run out to "K" but rows can vary from 8 to 80

Mike.

 

[mikemcbain]

I will test the suggestion by Fluff now.

 

[mikemcbain]

OK the Fluff option appears to be working perfectly for me so I will close this thread and once again thank everyone (and especially Fluff) for theior asistance.

Well done all!

Mike.

 

[Peter_SSs]

..t rows can vary from 8 to 80

OK, thanks, that is very small. I had envisaged a faster way if the data was very large but you wouldn't notice the difference with only this many rows.

 

[mikemcbain]

OK, thanks, that is very small. I had envisaged a faster way if the data was very large but you wouldn't notice the difference with only this many rows.

Peter

Many thanks for your interest and if I ever build a data set of past history I will come back to you for further assistance.

Mike.