parscon
09-19-2013, 11:09 AM
I have a VBA code that check the Column A on sheaet1 and check Column A on sheet2 and if found duplicate data will delete that row on sheet2
Now i have one problem and one request .
1- the problem is : when i have duplicate data on column A on sheet2 it will just delete the first one that founded and for example
Sheet1 Column A1 I have BOY and On Sheet2 Column A12 BOY and A34 BOY , when run my VBA code it will delete the BOY on A12 Sheet2 and will not delete the BOY on A34.
2- Request : Now i need to check all column in one row and if find duplicate data (that matched with data on column A on sheet1) on any column of that row on sheet2 delete them on sheet2.
For example : Sheet1 : A1 BOY Sheet2:B35 BOY it will delete that row.
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
Thank you very much.
Now i have one problem and one request .
1- the problem is : when i have duplicate data on column A on sheet2 it will just delete the first one that founded and for example
Sheet1 Column A1 I have BOY and On Sheet2 Column A12 BOY and A34 BOY , when run my VBA code it will delete the BOY on A12 Sheet2 and will not delete the BOY on A34.
2- Request : Now i need to check all column in one row and if find duplicate data (that matched with data on column A on sheet1) on any column of that row on sheet2 delete them on sheet2.
For example : Sheet1 : A1 BOY Sheet2:B35 BOY it will delete that row.
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
Thank you very much.