Sub RunMe() Dim lRow As Long Sheets("Sheet1").Select lRow = Range("P" & Rows.Count).End(xlUp).Row For Each cell In Range("P2:P" & lRow) 'Assuming you have a 1 row header If cell.Value = Sheets("Sheet2").Cells(cell.Row, "Q") Then Sheets("Sheet2").Range(Cells(cell.Row, "P"), Cells(cell.Row, "Q")).ClearContents End If Next cell End Sub
Sub RunMe() Dim lRow As Long Sheets("Sheet2").Select lRow = Range("Q1").End(xlDown).Row For Each cell In Range("Q2:Q" & lRow) x = 1 Do If cell.Value = Cells(cell.Row + x, "Q").Value Then Cells(cell.Row + x, "Q").ClearContents End If x = x + 1 Loop Until x = lRow + 1 Next cell End Sub
Sub RunMe() Dim lRow As Integer Sheets("Sheet1").Select lRow = Range("B" & Rows.Count).End(xlUp).Row For Each cell In Range("B2:B" & lRow) 'Assuming you have a 1 row header If cell.Value = Sheets("Sheet2").Cells(cell.Row, "B") Then Sheets("Sheet2").Cells(cell.Row, "B").ClearContents End If Next cell End Sub
DON'T MISS
What do you mean?
Sheet1-Master sheet
ColumnP
12
14
15
17
18
19
20
Sheet2
ColumnQ
12
13
1
2
8
9
9
8
7
7
20
19
Our macro will remove 12,20,19 respectively. But in sheet2 we can see duplicates(9,8,7) still there. I want to remove those duplicates also and replace them by blanks