Hyperlink Stripper: Strip Every Link From a Workpaper Before You Send It to the Client

TL;DR: Before you email a workpaper to a client, you need to remove every hyperlink — internal file paths, broken website links, and email addresses that reveal your firm’s internal network structure. Excel has no “remove all hyperlinks” command. This macro finds them all, shows you exactly where they are, and strips them in one click.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with hyperlinks — any workpaper you’re preparing to send outside the firm

What the macro does:

• Loops every sheet in ThisWorkbook and counts all hyperlinks
• Reports the exact count per sheet in a message box so you can verify before acting
• Asks once before deletion — one confirmation, not one per hyperlink
• Deletes all hyperlinks in a single pass

What the macro does NOT do:

• It does not remove the cell text. A cell that says “See Q2 reconciliation” with a hyperlink behind it will still say “See Q2 reconciliation” — just without the link
• It does not remove cell formatting. Blue underlined hyperlink-styled text stays blue and underlined. You can clear formatting manually or with a separate macro
• It does not touch external workbook links (formulas referencing other files). Use Strip External Links for those

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook (PERSONAL.XLSB) to run on any open file
• The ws.Hyperlinks.Delete method removes all hyperlinks on a sheet at once. There is no way to selectively keep some while deleting others — it’s all or nothing per sheet
• Hyperlink cell formatting (blue text, underline) is preserved. The link is gone but the cell still looks like a link. To strip formatting too, add a ws.Cells.Font.Underline = xlUnderlineStyleNone pass after deletion

#The Macro

Option Explicit

Sub StripHyperlinks()
    ' ── Hyperlink Stripper ─────────────────────────────
    ' Scans every sheet for hyperlinks. Reports the total
    ' count and which sheets are affected. One click to
    ' confirm and remove them all.
    '
    ' 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 hyperlinks across all sheets ──────
    total = 0
    sheetCount = 0
    sheetList = ""

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

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

    Dim msg As String
    msg = "Found " & total & " hyperlink(s) across " & _
          sheetCount & " sheet(s):" & vbCrLf & vbCrLf & _
          sheetList & vbCrLf & _
          "Remove all hyperlinks? This cannot be undone."

    ' ── Step 3: Confirm and delete ─────────────────────
    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm Hyperlink Removal") = vbNo Then
        MsgBox "No hyperlinks were removed.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    For Each ws In ThisWorkbook.Worksheets
        If ws.Hyperlinks.Count > 0 Then
            ws.Hyperlinks.Delete
        End If
    Next ws

    ' ── Step 4: Final report ───────────────────────────
    MsgBox "Removed " & total & " hyperlink(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 distinct passes — a counting pass and a deletion pass — separated by a confirmation message box. This is deliberate:

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

The first pass builds a complete inventory — every sheet, every hyperlink count — before touching anything. The message box then shows you exactly what will be deleted and where. You get to review the list and make an informed decision: “Are there 12 hyperlinks on the Fixed Assets sheet because the prior preparer linked to supporting documents, and I want to keep those? Or are they all broken C:\Users\... paths I need gone?”

If you click No, nothing happens. The macro exits without touching a single hyperlink. The counting pass is read-only.

#ws.Hyperlinks.Delete — the nuclear option

VBA has no “delete one hyperlink” method on the Hyperlinks collection. You can delete an individual hyperlink with ws.Hyperlinks(1).Delete, but there’s no way to filter which ones to delete based on the target address. So the macro uses the collection-level .Delete:

If ws.Hyperlinks.Count > 0 Then
    ws.Hyperlinks.Delete
End If

One line, all hyperlinks on the sheet, gone. The guard clause (If count > 0) is important — calling .Delete on a sheet with zero hyperlinks raises an error because there’s no collection to delete. The If check avoids that.

#Why the message box lists sheets by name

A bare “Found 47 hyperlinks. Remove all?” message is dangerous because you can’t verify whether those 47 hyperlinks are the ones you expected. Maybe you knew about the 8 hyperlinks in the Notes sheet and want to keep those — but the count includes them.

sheetList = sheetList & "  • " & ws.Name & _
            " (" & ws.Hyperlinks.Count & ")" & vbCrLf

Listing each sheet with its count lets you spot-check. If the Notes sheet shows 8 hyperlinks and you know that’s the partner’s review comments with file references you need, you can click No, unprotect Notes, remove those hyperlinks manually, then run the macro again to strip everything else.

#No Calculation toggle — same reason as the filter remover

This macro doesn’t write to any cells or change any values. It only deletes hyperlink objects from the sheet’s hyperlink collection. No formulas recalculate, no values shift, no cell contents change. Toggling Application.Calculation would add two lines (one to set, one to restore) for zero benefit.

Application.ScreenUpdating = False is still included because deleting hyperlinks triggers a sheet repaint. Without it, Excel redraws each sheet as hyperlinks are removed, causing visible flickering across multiple sheets.

#The confirmation uses vbExclamation — not vbQuestion

If MsgBox(msg, vbExclamation + vbYesNo, _
          "Confirm Hyperlink Removal") = vbNo Then

vbExclamation shows a yellow warning icon, not a blue question mark. The distinction matters: a question mark icon suggests “are you sure you want to do this routine thing?” An exclamation icon says “pay attention — this action is destructive.” Deleting hyperlinks is undoable, so the warning icon is the right choice.

This is a small UI detail but it trains the user’s brain: yellow icon = think before clicking. Blue icon = proceed normally. After running a dozen macros, the color coding becomes instinct.

#Hyperlink formatting survives deletion

When you delete a hyperlink with ws.Hyperlinks.Delete, the link is removed but the cell formatting remains. A cell that was blue and underlined will still be blue and underlined. The cell text is unchanged — if the hyperlink display text was “See workpaper,” the cell still says “See workpaper.”

This is usually what you want. The formatting indicates “this cell used to link to something” and gives the client visual cues without the security risk. If you want truly clean formatting, add a second pass after deletion that resets font styles. But for a “send to client” workflow, the formatting is harmless and the links are gone — which is what matters.

#Adapt It