Macro to delete entire Row on Sheet 1 if values found on Sheet 2 matches

[alextony]

I'm new to working with Excel Macros and I'm trying to find out how to create a macro to delete entire rows in Sheet 1, if any of the values found in ‘Sheet 2, column A’, matches any of the values found in ‘Sheet 1, column A’. I've been trying to figure this out for several hours. Can anyone help?

[Bob Phillips]

[vba]
With Worksheets("Sheet2")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

On Error Resume Next
pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"),0)
On Error Goto 0
If pos > 0 Then

Worksheets("Sheet1").Rows(i).Delete
End If
Next i
End With[/vba]

[alextony]

Thanks for replying on this. I tried it but I'm getting a syntax error on line 13. Can you tell me what I'm doing wrong? See below:

Sub Tony_RowDel()
With Worksheets("Sheet2")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
On Error Resume Next
Pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
On Error GoTo 0
If Pos > 0 Then
Worksheets("Sheet1").Rows(i).Delete
End If
Next i
End With
End Sub

[Bob Phillips]

My error, should have been Pos

[vba]

Sub Tony_RowDel()
With Worksheets("Sheet2")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
On Error Resume Next
Pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
On Error GoTo 0
If Pos > 0 Then
Worksheets("Sheet1").Rows(Pos).Delete
End If
Next i
End With
End Sub[/vba]

[alextony]

Still getting Run-time error '13'

Type Mismatch

When I look up this error on the internet, I get the following possible causes:

You need to convert the text to a numeric value before you can do anything with it.
So,
If cbo1.Text <= 12 Then
would need to be something like
If val(cbo1.Text) <= 12 Then
And
lbl1 = Val(cbo1.Text * 5)
something like
lbl1 = Val(cbo1.Text )* 5

--------------
Dim Val as Integer
If IsNumber(Cbo2.Text) then
Val = CType(Cbo2.Text, Integer)
End If

then use Val instead of cbo2.text

-----------------------------------------
Does any of this makes sense to you?

[Bob Phillips]

Where does text figure in your original code?

Can you post the workbook?

[alextony]

See abbreviated copy of the file attached. The original has about 25,000 rows in Sheet1 and about 1000 rows in Sheet2.

Thank you for hanging in there with me.

[Bob Phillips]

I meant a workbook with the code, I want to see what you have done.

[alextony]

Sorry, hear it is again with the code. See attached.

[Bob Phillips]

Try this

[vba]

Sub Tony_RowDel()
Dim rng As Range
Dim pos As Long

With Worksheets("Sheet2")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
pos = 0
On Error Resume Next
pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
On Error GoTo 0
If pos > 0 Then
If rng Is Nothing Then

Set rng = Worksheets("Sheet1").Rows(pos)
Else

Set rng = Union(rng, Worksheets("Sheet1").Rows(pos))
End If
End If
Next i

If Not rng Is Nothing Then rng.delet
End With
End Sub[/vba]

[alextony]

Wow, this worked! Thank you. The only issue I had is that since I had duplicate instances of the same value in Sheet1 Column A, I had to run the script several times until all the duplicates were removed. Nevertheless, it worked. I'll spend time figuring out exactly how you did it, so I can learn from this. Thank you again

[Bob Phillips]

If that is the case, you can invert the code, lookup from sheet1 and delete when match. That way it will only need 1 pass.

[alextony]

Thanks, I'll try to figure out how to do that. Still struggling as a newbie but I'll get there.

[Bob Phillips]

Just remember that when deleting in a loop on Sheet1, you need to work from the bottom up, not top down as usual in a loop.

[alextony]

Thank you. You've been a great help.