VBA code to clear contents of adjacent cells based on cell value

[Gavin Harrison]

Hi All.

I'm struggling to find the code to clear the contents of cells based on the content of another cell in the same row. For example:

I have a range of A1:C700.

If on any of these given rows, if in column C of that row the cell contains "Depart", I would like to clear the contents of columns A & B in the same row.

This would happen multiple times in the above range.

Thanks in advance all.

 

[Peter_SSs]

Assuming row 1 is a heading row (so C1 will not contain "Depart") the try

Sub ClearNearDepart()
  Application.ScreenUpdating = False
  With Range("A1:C700")
    .AutoFilter Field:=3, Criteria1:="Depart"
    With .Columns(1).Resize(, 2)
      If .SpecialCells(xlVisible).Count > 2 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).ClearContents
    End With
    .AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub