Delete row under condition

[Jeeremy7]

Hey Guys,

I'm going to start explaining this simple as possible, let me know if I don't make sense !

In my workbook there's sheet "A" and "B"
If the value on the same row for column F and G is the same on sheet "A" and "B" then delete that row in sheet "A"

Thanks !

 

[Marc L]

My demonstration revamped to match only columns D & E :​

Sub Demo1r()
        Dim C(), S$(), R&, V()
            C = [{4,5}]
    With Sheet2.[A1].CurrentRegion.Rows
            ReDim S(1 To .Count)
        For R = 1 To .Count
            S(R) = Join(Application.Index(.Item(R).Value2, 1, C))
        Next
    End With
    With Sheet1.[A1].CurrentRegion.Rows
            ReDim V(1 To .Count, 0)
        For R = 1 To .Count
            V(R, 0) = Join(Application.Index(.Item(R).Value2, 1, C))
        Next
           V = Application.Match(V, S, 0)
           R = Application.Count(V)
        If R Then
            Application.ScreenUpdating = False
           .Columns(.Columns.Count + 1).Value2 = Application.IsNumber(V)
           .Resize(, .Columns.Count + 1).Sort .Cells(1, .Columns.Count + 1), 1, Header:=2
            Union(.Item(.Count - R + 1 & ":" & .Count), .Columns(.Columns.Count + 1)).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Marc L]

The demonstration revised with a direct formula way :​

Sub Demo1r2d2()
      Const F = "D1:D#&""¤""&E1:E#"
        Dim V(), R&
    With Sheet2
        V = .Evaluate(Replace(F, "#", .[A1].CurrentRegion.Rows.Count))
    End With
    With Sheet1.[A1].CurrentRegion.Columns
           V = Application.Match(.Parent.Evaluate(Replace(F, "#", .Rows.Count)), V, 0)
           R = Application.Count(V)
        If R Then
            Application.ScreenUpdating = False
           .Item(.Count + 1).Value2 = Application.IsNumber(V)
           .Resize(, .Count + 1).Sort .Cells(1, .Count + 1), 1, Header:=2
            Union(.Rows(.Rows.Count - R + 1 & ":" & .Rows.Count), .Item(.Count + 1)).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Jeeremy7]

It is not working I have an error at
V = Application.Match(.Parent.Evaluate(Replace(F, "#", .Rows.Count)), V, 0)

Sheet1 in my example is name "PM TM" and sheet 2 is name "PM TD". I tried renaming them but I still have that error code
Is there a way for me to send an example with 2 different sheets ?

Thanks for you help !

 

[Marc L]

​

So link your workbook on a files host website like Dropbox for example …​

 

[Jeeremy7]

Dropbox

www.dropbox.com

Here's the link. If values on the same row match for column F and G then delete the line in "PM TM"

Thansk !

 

[Marc L]

(removed as I misread)

 

[Marc L]

Posting an attachment not well reflecting the real workbook like the one in post #7 means you are very confident​

with your Excel / VBA skills to amend any code in order to fit it to your real workbook ! As a reminder for your next thread …​

​

According to your post #15 link the last demonstration for starters :​

Sub Demo1bb8()
      Const F = "F6:F#&""¤""&G6:G#"
        Dim V, R&
    With Sheet15
        V = Filter(.Evaluate(Replace("TRANSPOSE(IF(F6:F#>""""," & F & "))", "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), False, False)
    End With
    With Sheet13.[A6].CurrentRegion.Columns
           V = Application.Match(.Parent.Evaluate(Replace(F, "#", .Rows(.Rows.Count).Row)), V, 0)
           R = Application.Count(V)
        If R Then
            Application.ScreenUpdating = False
           .Item(.Count + 1).Value2 = Application.IsNumber(V)
           .Resize(, .Count + 1).Sort .Cells(1, .Count + 1), 1, Header:=2
            Union(.Rows(.Rows.Count - R + 1 & ":" & .Rows.Count), .Item(.Count + 1)).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Jeeremy7]

The code doesn't seem to delete the correct lines. It deleted some headers and some lines that the columns weren't matching and kept some were the columns were matching :/

 

[Marc L]

​

No such issue on my side with your post #15 attachment …​

 

[Jeeremy7]

You're right I'm confused...
Now it gives me an error code at this place: .Resize(, .Count + 1).Sort .Cells(1, .Count + 1), 1, Header:=2

I don't understand the test I sent works perfectly...
In my real sheet, column D and E are not "XXX" but have actual names (I had to cut some data for confidentiality) Could it be that ?

Thanks for your time, it's really appreciated !!