Delete all cell comments in workbook

[Bill Carlson]

Excel 2007

We are a small cpa firm, it is tax season... We frequently copy last
year's workbook to set-up the current year. I'm trying to select and
delete all cell comments as part of this set-up process.

I've tried goto special and the comments section of the Review ribbon,
but I seemed to be stumped in trying to select all cells in the
workbook that contain a comment and then to delete these comments
without cycling through the next comment for each comment on the
Review ribbon.

As always, help is much appreciated,

Bill Carlson

 

[Gord Dibben]

I had no problem selecting all sheets and all cells then
F5>Special>Comments>OK

Review>Delete cleared all Comments on all sheets.


Gord Dibben MS Excel MVP

 

[מיכ×ל (מיקי) ×בידן]

The following VBA Macro should do what you are looking for:
------------------------------------------------
Sub Del_All_Comments()
For Each SH In ActiveWorkbook.Sheets
For Each Cmt In SH.Comments
Cmt.Delete
Next
Next
End Sub

 

[Bill Carlson]

I had no problem selecting all sheets and all cells then
F5>Special>Comments>OK

Review>Delete cleared all Comments on all sheets.

Gord Dibben  MS Excel MVP







- Show quoted text -

Hi Gord, must be doing something wrong. I have a sample workbook,
three sheets, comments in cell A1, Sheet 1, B2, Sheet2 and C3 in Sheet
3. When I select all sheets, select all cells, F5>Special>Comments OK
Review>Delete, it only deleted the comment of the active sheet --

bill

 

[Bill Carlson]

The following VBA Macro should do what you are looking for:
------------------------------------------------
Sub Del_All_Comments()
        For Each SH In ActiveWorkbook.Sheets
              For Each Cmt In SH.Comments
                    Cmt..Delete
              Next
        Next
End Sub
--------------
Micky








- Show quoted text -

Hi Micky, after I declared the variables SH as Worksheet and Cmt as
Comment, I was good to go.

thank you,

bill

 

[Gord Dibben]

You did nothing wrong.

My testing was faulty.

Apologies for that.

I see you have gone with Micky's macro which does the job.


Gord