Delete Comment if contents = "HIDDEN FORMULA"

[Guest]

Excel Experts,

A spreadsheet I inherited is overrun with comments which say "HIDDEN
FORMULA!" and I'd like to delete all of these comments. The spreadsheet has
some useful comments so I don't want to delete every comment.

When I used the macro recorder to figure out the Text of these comments, I
got a line back

Range("A1").Comment.Text Text:="HIDDEN FORMULA!" & Chr(10) & ""

So I think my code should be something like,

Dim C as Comment
For Each C in ActiveSheet.Comments
If Comment.Text = ""HIDDEN FORMULA!" & Chr(10) & """
CellofComment.ClearComments
End Sub

I realize the "CellofComment" isn't the right way to reference the cell
which contains the comment.

Thanks in advance.

Alan

 

[Norman Jones]

Hi Alan,

Try:

'==================>>
Sub TesterA()
Dim C As Comment
For Each C In ActiveSheet.Comments
If InStr(1, C.Text, _
"HIDDEN FORMULA!", vbTextCompare) Then
C.Delete
End If
Next C

End Sub
'<<==================

 

[Guest]

Norman,

Worked perfectly.

Thanks, Alan