All Comments Deleter: Remove Every Review Note Before Sending a Workpaper to the Client

TL;DR: Before you send a workpaper to the client, you need to remove every internal review note — preparer explanations, reviewer questions, partner sign-offs. Excel has no “delete all comments” command. This macro finds them all, shows you exactly where they are, and strips them in one confirmation click. Run Comment Collector first to save the notes to a report, then run this macro to clean the file.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with cell comments that you want to remove before sending to a client
• Important: Run Comment Collector first to archive notes as a PDF report. This macro permanently deletes comments — there is no undo

What the macro does:

• Loops every sheet in ThisWorkbook and counts all cell comments
• Reports the exact count per sheet in a message box so you can verify before acting
• Suggests running Comment Collector first as a safety reminder
• Asks once before deletion — one confirmation, not one per comment
• Deletes all comments across all sheets using ws.Cells.ClearComments

What the macro does NOT do:

• It does not collect comments before deleting. Run Comment Collector first
• It does not remove threaded comments (modern Excel 365’s reply-thread system). Threaded comments use a separate object model and are not in the Comments collection
• It does not remove cell content. If a cell has both a value and a comment, only the comment is deleted — the cell value remains

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook (PERSONAL.XLSB) to run on any open file
• There is no undo. Once ClearComments runs, the comments are gone permanently. This is by design — the confirmation MsgBox is your safety net
• Deletion is all-or-nothing per workbook. You cannot selectively keep some comments while deleting others. If you need to preserve certain comments, save a copy of the file first

#The Macro

Option Explicit

Sub DeleteAllComments()
    ' ── All Comments Deleter ──────────────────────────
    ' Scans every sheet for cell comments. Reports the
    ' count and which sheets are affected. One click to
    ' confirm and remove them all.
    '
    ' Destructive. Run Comment Collector first.
    ' Zero configuration. One confirmation. All clear.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim total As Long
    Dim sheetCount As Long
    Dim sheetList As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Count comments across all sheets ───────
    total = 0
    sheetCount = 0
    sheetList = ""

    For Each ws In ThisWorkbook.Worksheets
        If ws.Comments.Count > 0 Then
            sheetCount = sheetCount + 1
            sheetList = sheetList & "  • " & ws.Name & _
                        " (" & ws.Comments.Count & ")" & vbCrLf
            total = total + ws.Comments.Count
        End If
    Next ws

    ' ── Step 2: Report findings ────────────────────────
    If total = 0 Then
        MsgBox "No comments found in any sheet.", _
               vbInformation, "All Clear"
        GoTo CleanUp
    End If

    Dim msg As String
    msg = "Found " & total & " comment(s) across " & _
          sheetCount & " sheet(s):" & vbCrLf & vbCrLf & _
          sheetList & vbCrLf & _
          "⚠ Tip: Run Comment Collector first to save" & _
          " notes before deleting." & vbCrLf & vbCrLf & _
          "Delete all comments? This cannot be undone."

    ' ── Step 3: Confirm and delete ─────────────────────
    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm Comment Deletion") = vbNo Then
        MsgBox "No comments were deleted.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    For Each ws In ThisWorkbook.Worksheets
        If ws.Comments.Count > 0 Then
            ws.Cells.ClearComments
        End If
    Next ws

    ' ── Step 4: Final report ───────────────────────────
    MsgBox "Deleted " & total & " comment(s) from " & _
           sheetCount & " sheet(s).", vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Count first, delete later

The macro runs in two passes — a read-only counting pass and a deletion pass — separated by a confirmation message box. You get a complete inventory of every comment before anything is touched.

For Each ws In ThisWorkbook.Worksheets
    If ws.Comments.Count > 0 Then
        sheetCount = sheetCount + 1
        sheetList = sheetList & "  • " & ws.Name & _
                    " (" & ws.Comments.Count & ")" & vbCrLf
        total = total + ws.Comments.Count
    End If
Next ws

The message box lists every sheet with comments and the count on each. If the Fixed Assets sheet shows 4 comments and you know the partner’s sign-off is there and you want to keep it, you can click No, save the file, strip comments from the other sheets manually, then run the macro to clean up the rest.

This is the same pattern used in Hyperlink Stripper and All Shapes Deleter. Count everything, show the user, get confirmation, then act.

#ws.Cells.ClearComments — one line per sheet

If ws.Comments.Count > 0 Then
    ws.Cells.ClearComments
End If

ClearComments is the most efficient way to delete all comments on a sheet. It clears every comment in one call — no need to loop through each Comment object individually. The guard clause (If count > 0) avoids calling ClearComments on sheets with no comments, which is harmless but unnecessary.

ClearComments was introduced in Excel 2010 and is faster than looping through ws.Comments and calling .Delete on each. For a sheet with 20 comments, the difference is negligible. For a workbook with 200 comments across 10 sheets, ClearComments is noticeably faster.

#The Comment Collector reminder

The confirmation message box includes a tip:

msg = msg & vbCrLf & _
      "⚠ Tip: Run Comment Collector first to save" & _
      " notes before deleting."

This is deliberate. The Comment Collector macro is this macro’s companion — it extracts every comment into a printable report before you delete them. If the user runs this macro without collecting comments first, they lose valuable review history. The reminder in the confirmation dialog gives them a last chance to cancel, run Comment Collector, archive the notes, and then come back.

Note there’s no automated Comment Collector call. Calling one macro from another in Excel requires both macros to be in the same module or available in the same workbook. Since this macro may live in Personal Macro Workbook and Comment Collector may be in a different module, the reminder is safer than a failed cross-reference.

#The confirmation uses vbExclamation — same reason as Hyperlink Stripper

If MsgBox(msg, vbExclamation + vbYesNo, _
          "Confirm Comment Deletion") = vbNo Then

vbExclamation shows a yellow warning icon, not a blue question mark. The yellow icon signals “this action is destructive and cannot be undone.” Comments are permanently deleted — no undo, no recovery (unless you saved the comments first with Comment Collector). The warning icon primes the user to think before clicking Yes.

#No Calculation toggle

This macro doesn’t write to any cells or change any values. It only clears the comment objects from each sheet’s comment collection. No formulas recalculate, no cell values change. Toggling Application.Calculation would add two lines for zero benefit.

Application.ScreenUpdating = False is included because clearing comments triggers a sheet repaint. Without it, Excel redraws each sheet as comments are removed, causing visible blinking across multiple sheets.

#The cancelled path reports nothing happened

If MsgBox(msg, vbExclamation + vbYesNo, ...) = vbNo Then
    MsgBox "No comments were deleted.", _
           vbInformation, "Cancelled"
    GoTo CleanUp
End If

When the user clicks No, a second MsgBox confirms that nothing happened. This is important for trust. If the macro exits silently on cancel, the user might wonder “did it delete them anyway?” The explicit “No comments were deleted” message removes all doubt.

#Adapt It