Delete Comment if contents = "HIDDEN FORMULA"

[achidsey]

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

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

---
Regards,
Norman



"achidsey" <chidsey2@hotmail.com(notmorespam)> wrote in message
news:D23CD6BC-05D6-4513-B39F-3C15D9BD0046@microsoft.com...
> 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

[achidsey]

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" <chidsey2@hotmail.com(notmorespam)> wrote in message
> news:D23CD6BC-05D6-4513-B39F-3C15D9BD0046@microsoft.com...
> > 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
>
>
>