PDA

View Full Version : Solved: find the data on 2 sheet delete the row of second sheet



parscon
11-20-2012, 01:33 PM
Dear Friend ,

I need your helps ,


I have excel file that have 2 sheet . 1 sheet have one column A and another sheet have some other data

I want to check sheet2 if find any data of sheet1 on column A in any column of sheet2 delete the row of that sheet(sheet2)

for example if I have number 2243232 in column A of sheet1 and also I have the same data in sheet 2 but in column J of row 420 , delete the row 420.

(if find he same data in any column of that row delete that row on sheet2)

Hope you understand what I need .

parscon
11-21-2012, 07:09 AM
Special Thank to mancubus


Sub DelMatchedRows()

Dim LastRow As Long, i As Long, mRow As Long

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
On Error Resume Next
mRow = Application.Match(.Cells(i, 1), Worksheets("Sheet2").Columns(1), 0)
If mRow > 0 Then Worksheets("Sheet2").Rows(mRow).EntireRow.Delete
Next
End With

End Sub