PDA

View Full Version : Macro to delete entire Row on Sheet 1 if values found on Sheet 2 matches



alextony
02-07-2012, 10:20 AM
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
02-07-2012, 10:33 AM
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

alextony
02-07-2012, 03:35 PM
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
02-07-2012, 04:03 PM
My error, should have been Pos



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

alextony
02-07-2012, 10:19 PM
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
02-08-2012, 02:08 AM
Where does text figure in your original code?

Can you post the workbook?

alextony
02-08-2012, 04:56 AM
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
02-08-2012, 05:03 AM
I meant a workbook with the code, I want to see what you have done.

alextony
02-08-2012, 07:01 AM
Sorry, hear it is again with the code. See attached.

Bob Phillips
02-08-2012, 08:07 AM
Try this



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

alextony
02-08-2012, 01:31 PM
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 :thumb

Bob Phillips
02-08-2012, 04:23 PM
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
02-09-2012, 09:49 AM
Thanks, I'll try to figure out how to do that. Still struggling as a newbie but I'll get there.

Bob Phillips
02-09-2012, 05:35 PM
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
02-13-2012, 02:13 PM
Thank you. You've been a great help.