Clears the content of a cell in a row

  • Thread starter Thread starter Yee
  • Start date Start date
Y

Yee

I currently have a mini marco that clears the contents of
a specific cell if a relative cell contains a specific
value. This is the macro:

With ActiveSheet
If Range("A2") = "Hello" Then
Range("A1").ClearContents
End If
End With

End Sub

How could I re write the macro so that any cell in Row 2
that contains "Hello" would have the relative cell in Row
1 cleared?

For example, if cell A2 contains "Hello", then cell A1
could be cleared. If B2 contains "Hello", cell B1 would
be cleared.....Hope you understand what I mean...Many
thanks.
 
one way:

Public Sub ClearAboveIfHello()
Const sFIND As String = "Hello"
Dim rCell As Range
On Error Resume Next 'in case no text found
For Each rCell In Rows(2).SpecialCells( _
xlCellTypeConstants, xlTextValues)
With rCell
If .Value = sFIND Then .Offset(-1, 0).ClearContents
End With
Next rCell
End Sub
 
Hi
one way

Dim rng as range
Dim cell as range
set rng = activesheet.range("1:1")
for each cell in rng
if rng.value = "Hello" then
rng.offset(1,0).clearcontents
next
 
Back
Top