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 !

 

[dave3009]

I think this will do as you have described

Sub DeleteOnSameRow()
Dim lst As Long
Dim i As Long
Dim testA As String
Dim testB As String

xlSettings False

lst = Sheets("B").Range("F1").CurrentRegion.Rows.Count

For i = lst To 2 Step -1
    testA = Sheets("A").Range("F" & i).Value & Sheets("A").Range("G" & i).Value
    testB = Sheets("B").Range("F" & i).Value & Sheets("B").Range("G" & i).Value
    
    If testA = testB Then
        Sheets("A").Rows(i).Delete xlUp
    End If
Next i

xlSettings True

End Sub

Sub xlSettings(t As Boolean)
With Application
    .ScreenUpdating = t
    .EnableEvents = t
    .Calculation = IIf(t, xlCalculationAutomatic, xlCalculationManual)
End With
End Sub

 

[Jeeremy7]

Thanks a lot I will try this as soon as I can and let you know !

 

[Jeeremy7]

I think this will do as you have described

Sub DeleteOnSameRow()
Dim lst As Long
Dim i As Long
Dim testA As String
Dim testB As String

xlSettings False

lst = Sheets("B").Range("F1").CurrentRegion.Rows.Count

For i = lst To 2 Step -1
    testA = Sheets("A").Range("F" & i).Value & Sheets("A").Range("G" & i).Value
    testB = Sheets("B").Range("F" & i).Value & Sheets("B").Range("G" & i).Value
   
    If testA = testB Then
        Sheets("A").Rows(i).Delete xlUp
    End If
Next i

xlSettings True

End Sub

Sub xlSettings(t As Boolean)
With Application
    .ScreenUpdating = t
    .EnableEvents = t
    .Calculation = IIf(t, xlCalculationAutomatic, xlCalculationManual)
End With
End Sub

Hmm not sure why but nothing happened when I tried to run the Macro :/

 

[Jeeremy7]

Would anyone know how to make this work ?

 

[Marc L]

Hi,​

​

without any attachment it's just a guessing challenge for some mind readers forum …​

 

[Jeeremy7]

Here's an example, sheet 1 and 2 and the letters would be the columns.
In yellow is what would need to deleted (In that example when column "D" and "E" of the 2 different sheets would match

Thanks

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P
5	SHEET 1							SHEET 2
6	A	B	C	D	E	F		A	B	C	D	E	F
7	xxx	xxx	xxx	aaa	ccc	xxx		xxx	xxx	xxx	aaa	ccc	xxx
8	xxx	xxx	xxx	aaa	hhh	xxx		xxx	xxx	xxx	lll	ddd	xxx
9	xxx	xxx	xxx	lll	ddd	xxx		xxx	xxx	xxx	bbb	ddd	xxx
10	xxx	xxx	xxx	bbb	ddd	xxx		xxx	xxx	xxx	ddd	fff	xxx
11	xxx	xxx	xxx	iii	ddd	xxx		xxx	xxx	xxx	aaa	ddd	xxx
12	xxx	xxx	xxx	ddd	fff	xxx
13	xxx	xxx	xxx	ppp	ddd	xxx
14	xxx	xxx	xxx	aaa	ddd	xxx
Sheet1

 

[Marc L]

​

How many rows in real workbook in each worksheet ?​

 

[Jeeremy7]

It could be a couple thousands

 

[Marc L]

According to your attachment a VBA demonstration for starters :​

Sub Demo1()
        Dim R&, S$(), V()
    With Sheet2.[A1].CurrentRegion.Rows
            ReDim S(1 To .Count)
        For R = 1 To .Count
            S(R) = Join(Application.Index(.Item(R).Value2, 1, 0))
        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, 0))
        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