Macro to delete entirerow from Sheet2 if Sheet1 contains value ?

[Coyotex3]

Hello, I'm trying to create a macro that will look At sheet1 "Column A" and go down each cell with data and if on Sheet1 Range("A6").value = "James" then look at sheet2 and delete the entire row if it finds "James" on sheet2.
If Range("A10").value = "Jane" then delete the entire row if it finds "Jane" on sheet2 etc.

Like This

Book1
	A	B	C	D	E
1	Report
2	8-Feb
3	User
4
5	Name	Order	Amount
6	James	10	100
7	James	15	150
8	James	15	150
9	James	15	150
10
11
12
13	John	20	200
14	John	20	200
15	John	20	200
16	John	20	200
17	John	20	200
18	John	20	200
19	John	20	200
20	John	20	200
21
22
23
24	Jane	20	200
25	Jane	20	200
26	Jane	20	200
27	Jane	20	200
28	Jane	20	200
29	Jane	20	200
30	Jane	20	200
31	Jane	20	200
32
33
34
35
36
37
38
Sheet1

To This

Book1
	A	B	C	D
1	Report
2	8-Feb
3	User
4
5	Name	Order	Amount
6	John	20	200
7	John	20	200
8	John	20	200
9	John	20	200
10	John	20	200
11	John	20	200
12	John	20	200
13	John	20	200
14
15
Sheet1

Sheet2 will look like this

Book1
	A	B	C
1	Names
2	James
3	James
4	James
5	Jane
6	Jane
7	Jane
Sheet2

Any ideas?

 

[Coyotex3]

Sorry, I meant to say that I need a macro to look At sheet2 "Column A" and go down each cell with data and if on Sheet2 Range("A6").value = "James" then look at sheet1 and delete the entire row if it finds "James" on sheet1.

 

[Coyotex3]

Bumping.

 

[Coyotex3]

Bumping.

Anybody?

Last try

 

[Coyotex3]

Here is what I have came up so far.

Sub Delete()
 Dim donot(2) As Variant
 Dim sh As Worksheet
 Dim sht As Worksheet
 
 Set sh = Sheets("sheet2")
  Set sht = Sheets("sheet1")
 
 donot(1) = sh.Range("A2").Value
 donot(2) = sh.Range("A4").Value
With ActiveSheet
    For i = 1 To 2
        .AutoFilterMode = False
        With Range("A6", Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*" & donot(i) & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    Next i
End With
End Sub

Can anybody help me make it dynamic? Sometimes sheet2 will have more than 2 names. It may have 2 this week but 15 next week.

 

[Coyotex3]

Found this code on StackOverflow and with a little tailoring I was able to get it to do what I needed.

Sub delete_selected_rows()
    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow As Long

    With Worksheets("sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng1 = .Range("A6:A" & lastRow)
    End With

    Set rng2 = Worksheets("sheet2").Range("A:A")

    For Each c In rng1
        If Not IsError(Application.Match(c.Value, rng2, 0)) Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c

    If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub