Loop 2 sheets, Clear Cell Contents

[panyagak]

HELLO ALL & PROSPEROUS 2023!!

I have 2 sheets. My column of interest is Coln B.
I need to loop Sheet 1 against Sheet 2 for a MATCH in Coln B and if found, Clear Contents of the cell only (Blank).

Regards
Patrick

 

[HaHoBe]

Hi Patrick,

search on Column B on both sheets, clear on which sheet?

Public Sub MrE_1226077_170050B_01()
' https://www.mrexcel.com/board/threads/loop-2-sheets-clear-cell-contents.1226077/
'/// Please note: ALWAYS run the macros on a copy of your Workbook !!!!

'Clear On Worksheet1
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngTot As Range
Dim rngCell As Range

'Change sheetnames to suit
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

With ws1
  Set rngTot = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp))
End With

For Each rngCell In rngTot
  If WorksheetFunction.CountIf(ws2.Columns(2), rngCell.Value) > 0 Then
    rngCell.Value = vbNullString
  End If
Next rngCell

Set rngTot = Nothing
Set ws2 = Nothing
Set ws1 = Nothing
End Sub

Public Sub MrE_1226077_170050B_02()
' https://www.mrexcel.com/board/threads/loop-2-sheets-clear-cell-contents.1226077/
'/// Please note: ALWAYS run the macros on a copy of your Workbook !!!!

'Clear On Worksheet2
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngTot As Range
Dim rngCell As Range
Dim varRet As Variant

'Change sheetnames to suit
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

With ws1
  Set rngTot = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp))
End With

For Each rngCell In rngTot
  Do While WorksheetFunction.CountIf(ws2.Columns(2), rngCell.Value) > 0
    varRet = Application.Match(rngCell.Value, ws2.Columns(2), 0)
    If Not IsError(varRet) Then ws2.Cells(varRet, 2).Value = vbNullString
  Loop
Next rngCell

Set rngTot = Nothing
Set ws2 = Nothing
Set ws1 = Nothing
End Sub

Ciao,
Holger

 

[panyagak]

Sheet 2. Thank you