Delete Comment if contents = "HIDDEN FORMULA"
 

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

--
achidsey

Delete Comment if contents = "HIDDEN FORMULA"
 

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
'<<==================

---
Regards,
Norman



"achidsey" (notmorespam) wrote in message
...
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

--
achidsey

Delete Comment if contents = "HIDDEN FORMULA"
 

Norman,

Worked perfectly.

Thanks, Alan


--
achidsey


"Norman Jones" wrote:

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
'<<==================

---
Regards,
Norman



"achidsey" (notmorespam) wrote in message
...
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

--
achidsey