Dim NbLigneSheet1, NbLigneSheet2, LigneDbtData1, LigneDbtData2, a, b As Integer
Dim F1, F2 As Object
 
'Procédure principale
Sub SuppressionLignes()
 
'On appelle les procédures qui initialisent les variables
Call InitVar
Call CalculNbLignes
 
'On parcourt la première feuille
For a = LigneDbtData1 To NbLigneSheet1
 
'Pour chaque ligne de la première feuille, on parcourt complètement la deuxième feuille
For b = LigneDbtData2 To NbLigneSheet2
 
'On compare les contenus des deux feuilles
VarNom = F1.Range("B" & a) & " " & F1.Range("C" & a)
 
If VarNom = F2.Range("D" & b) Then
 
'En cas de correspondance on supprime la ligne correspondante dans la deuxième feuille
F2.Range("A" & b).Select
Selection.EntireRow.Delete
'Ne pas oublier de décrémenter b sinon on zappe une ligne
b = b - 1
 
End If
 
Next b
Next a
 
End Sub
 
 
'Cette procédure sert à regrouper les variables à initialiser
Sub InitVar()
 
Set F1 = Sheet1
Set F2 = Sheet2
LigneDbtData1 = 3
LigneDbtData2 = 3
 
 
End Sub
 
'Cette procédure permet de calculer le nombre de lignes dans les tableaux contenant les noms
Sub CalculNbLignes()
 
F1.Select
NbLigneSheet1 = Range("B65536" ).End(xlUp).Row
 
F2.Select
NbLigneSheet2 = Range("D65536" ).End(xlUp).Row
 
End Sub
 

Sub main()

Dim i As Integer, j As Integer
Dim sheet1_name As String, sheet2_name As String, sheet3_name As String, name As String
Dim delete As Boolean

sheet1_name = "Feuil1"
sheet2_name = "Feuil2"
sheet3_name = "Feuil3"

'stock name sheet1 in sheet3 col1
With ThisWorkbook.Worksheets(sheet1_name)
    For i = 1 To .Cells(1, 1).End(xlDown).Row
        ThisWorkbook.Worksheets(sheet3_name).Cells(i, 1).Value = .Cells(i, 1).Value
    Next i
End With
'stock name sheet2 in sheet3 col2
With ThisWorkbook.Worksheets(sheet2_name)
    For i = 1 To .Cells(1, 1).End(xlDown).Row
        ThisWorkbook.Worksheets(sheet3_name).Cells(i, 2).Value = .Cells(i, 1).Value
    Next i
End With

'delete sheet1
With ThisWorkbook.Worksheets(sheet1_name)
    For i = .Cells(1, 1).End(xlDown).Row To 1 Step -1
        name = .Cells(i, 1).Value
        delete = False
        With ThisWorkbook.Worksheets(sheet3_name)
            For j = .Cells(1, 2).End(xlDown).Row To 1 Step -1
                If name = .Cells(j, 2).Value Then
                    delete = True
                    Exit For
                End If
            Next j
        End With
        If delete = True Then .Rows(i).delete
    Next i
End With

'delete sheet2
With ThisWorkbook.Worksheets(sheet2_name)
    For i = .Cells(1, 1).End(xlDown).Row To 1 Step -1
        name = .Cells(i, 1).Value
        delete = False
        With ThisWorkbook.Worksheets(sheet3_name)
            For j = .Cells(1, 1).End(xlDown).Row To 1 Step -1
                If name = .Cells(j, 1).Value Then
                    delete = True
                    Exit For
                End If
            Next j
        End With
        If delete = True Then .Rows(i).delete
    Next i
End With

End Sub

Sub main2()

Dim i As Integer
Dim sheet1_name As String, sheet2_name As String
Dim sname As Variant
Dim dico As Scripting.Dictionary

Set dico = New Scripting.Dictionary
sheet1_name = "Feuil1"
sheet2_name = "Feuil2"
dico(sheet1_name) = sheet2_name
dico(sheet2_name) = sheet1_name

For Each sname In Array(sheet1_name, sheet2_name)
    With ThisWorkbook.Worksheets(sname)
        For i = 1 To .Cells(1, 1).End(xlDown).Row
            dico(sname & .Cells(i, 1).Value) = i
        Next i
    End With
Next sname

For Each sname In Array(sheet1_name, sheet2_name)
    With ThisWorkbook.Worksheets(sname)
        For i = .Cells(1, 1).End(xlDown).Row To 1 Step -1
            If dico.Exists(dico(sname) & .Cells(i, 1).Value) = True Then .Rows(i).delete
        Next i
    End With
Next sname

Set dico = Nothing
End Sub