Delete data from 2 columns if one column contains a word

[tonywatsonhelp]

Hi Everyone,

I need a macro that looks down column D and if a cell value is "Sold" deletes that cell and the one next to it (Column C)

thanks

Tony

 

[alansidman]

Option Explicit


Sub Tony()
Dim i As Long, lr As Long
lr = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Range("D" & i) = "Sold" Then
Range("C" & i & ":D" & i).ClearContents
End If
Next i
End Sub

 

[tonywatsonhelp]

Thank you Alansidman
That's a great help
Tony

 

[Peter_SSs]

Don't know how many rows you might have to cycle through like that but you could clear them all at once (assuming a heading row).

Sub Clear_Sold()
  Application.ScreenUpdating = False
  With Range("C1", Range("D" & Rows.Count).End(xlUp))
    .AutoFilter Field:=2, Criteria1:="Sold"
    If .SpecialCells(xlVisible).Cells.Count > 2 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub