Data Validation Stripper: Clear Every Leftover Dropdown and Input Restriction From an Inherited Workbook

TL;DR: You open an inherited workbook and some cells refuse your input. They have data validation rules left behind by the prior preparer — dropdown lists that reference deleted ranges, date restrictions that block legitimate entries, and whole-number limits on cells that need text. One macro finds them all, tells you exactly which sheets are affected, and strips every rule in one pass.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with data validation rules — any inherited file where cells refuse your input or show unexpected dropdown arrows

What the macro does:

• Loops every sheet in ThisWorkbook and checks for data validation rules
• Counts how many sheets have validation and lists them by name
• Asks once before deletion — one confirmation, not one per sheet
• Removes all data validation rules from every sheet in a single pass

What the macro does NOT do:

• It does not change cell values or formatting. A cell that displayed “Real Property” due to a dropdown will still say “Real Property” — you just can’t pull up the dropdown anymore
• It does not unprotect sheets. If a sheet is protected, the macro skips it and lists it as skipped in the final report
• It does not remove conditional formatting — that’s a separate feature (Home → Conditional Formatting → Clear Rules)

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook (PERSONAL.XLSB) to run it on any open file
• Protected sheets are skipped. The macro uses On Error Resume Next for the .Validation.Delete call and reports which sheets could not be cleaned
• Excel table (ListObject) column validation is separate from worksheet-level validation. This macro clears worksheet-level validation; table-level rules on ListObject columns are not affected

#The Macro

Option Explicit

Sub ClearAllValidation()
    ' ── Data Validation Stripper ──────────────────────
    ' Scans every sheet for data validation rules, lists
    ' which sheets are affected, and removes all rules
    ' with one confirmation.
    '
    ' Zero configuration. One confirmation. All clear.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim sheetCount As Long
    Dim affectedSheets As String
    Dim skippedSheets As String
    Dim skipCount As Long

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

    ' ── Step 1: Check for validation on visible sheets ─
    sheetCount = 0
    skipCount = 0
    affectedSheets = ""
    skippedSheets = ""

    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then
            skipCount = skipCount + 1
            GoTo NextSheet
        End If

        ' Check if the sheet has validation by inspecting
        ' the UsedRange's Validation collection
        On Error Resume Next
        Dim vType As Long
        vType = ws.UsedRange.Validation.Type
        On Error GoTo CleanUp

        If Err.Number = 0 Then
            ' Validation exists on this sheet
            sheetCount = sheetCount + 1
            affectedSheets = affectedSheets & "  • " & ws.Name & vbCrLf
        End If
        Err.Clear

        ' Also check if the sheet is protected (will fail on delete)
        If ws.ProtectContents Then
            skippedSheets = skippedSheets & "  • " & ws.Name & _
                            " (protected)" & vbCrLf
        End If

NextSheet:
    Next ws

    ' ── Step 2: Report findings ────────────────────────
    If sheetCount = 0 Then
        MsgBox "No data validation rules found on any sheet.", _
               vbInformation, "All Clear"
        GoTo CleanUp
    End If

    Dim msg As String
    msg = "Found data validation on " & sheetCount & _
          " sheet(s):" & vbCrLf & vbCrLf & affectedSheets & vbCrLf & _
          "Remove ALL data validation rules? This action cannot be undone."

    ' ── Step 3: Confirm and delete ─────────────────────
    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm — Remove All Validation") = vbNo Then
        MsgBox "No validation rules were removed.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    Dim removed As Long
    Dim failed As Long
    removed = 0
    failed = 0
    skippedSheets = ""

    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then GoTo DeleteNext

        ' Check for validation before attempting delete
        On Error Resume Next
        vType = ws.UsedRange.Validation.Type
        On Error GoTo CleanUp

        If Err.Number <> 0 Then
            Err.Clear
            GoTo DeleteNext
        End If

        ' Attempt to delete validation
        On Error Resume Next
        ws.Cells.Validation.Delete
        If Err.Number = 0 Then
            removed = removed + 1
        Else
            failed = failed + 1
            skippedSheets = skippedSheets & "  • " & ws.Name & _
                            " — " & Err.Description & vbCrLf
            Err.Clear
        End If
        On Error GoTo CleanUp

DeleteNext:
    Next ws

    ' ── Step 4: Final report ───────────────────────────
    Dim result As String
    result = "Removed data validation from " & removed & _
             " sheet(s)."

    If failed > 0 Then
        result = result & vbCrLf & vbCrLf & _
                 failed & " sheet(s) skipped:" & vbCrLf & skippedSheets
    End If

    MsgBox result, 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 — same pattern as the hyperlink stripper

Like the Hyperlink Stripper, this macro runs two passes separated by a confirmation message box. The first pass builds an inventory of every sheet with validation rules without touching anything. The second pass — only if the user clicks Yes — removes them all.

vType = ws.UsedRange.Validation.Type

ws.UsedRange.Validation.Type returns a numeric code representing the validation type on the sheet’s used range. If the used range has no validation, accessing .Type raises an error, which the macro catches with On Error Resume Next. If Err.Number = 0, validation exists and the sheet is added to the list.

This is a fast check that works on the entire used range at once — no need to loop through every cell.

#Why Validation.Type instead of SpecialCells

An alternative approach uses ws.Cells.SpecialCells(xlCellTypeAllValidation). This returns a range of every cell with validation, and checking Not (rng Is Nothing) confirms existence. But SpecialCells with xlCellTypeAllValidation fails on sheets with no validation — you get an error, not an empty range. The Validation.Type approach is simpler: one property access, one error check.

However, SpecialCells has an advantage if you want to report the COUNT of cells with validation per sheet, not just whether the sheet has any. See the Adapt It section for a variant that reports cell counts.

#Protected sheets — the message box warns you

If a sheet is protected (ws.ProtectContents = True), the macro can see that validation exists but may not be able to delete it. The first pass notes protected sheets in the confirmation message:

If ws.ProtectContents Then
    skippedSheets = skippedSheets & "  • " & ws.Name & _
                    " (protected)" & vbCrLf
End If

The deletion pass uses On Error Resume Next for each .Validation.Delete call, so protected sheets don’t crash the macro — they’re just skipped and reported.

#ws.Cells.Validation.Delete vs ws.UsedRange.Validation.Delete

The macro uses ws.Cells.Validation.Delete to remove validation from every cell on the sheet, including ones outside the used range that may have had validation applied and then data deleted. ws.UsedRange.Validation.Delete only clears the used range — if a cell in row 500 has validation but row 500 is outside the used range because all data was cleared, the old validation rule would survive. Using ws.Cells is slightly slower but guarantees complete cleanup.

#Why there’s no Calculation toggle

Stripping data validation doesn’t change any cell values — it only removes a formatting constraint. No formulas need to recalculate, no values shift, no cell content changes. Toggling Application.Calculation adds two lines for zero benefit.

Application.ScreenUpdating = False is still included because removing validation triggers a sheet redraw. Without it, Excel repaints each sheet as validation rules are cleared.

#The confirmation uses vbExclamation — same design principle

If MsgBox(msg, vbExclamation + vbYesNo, ...) = vbNo Then

Deleting validation rules is destructive — there’s no undo. The yellow warning icon (vbExclamation) signals “pay attention.” After running several macros on this blog, readers learn the color coding instinctively: yellow = think before clicking.

#Why the macro skips hidden and very-hidden sheets

The first thing the loop checks is ws.Visible <> xlSheetVisible. Hidden sheets (xlSheetHidden) and very-hidden sheets (xlSheetVeryHidden) are skipped because:

1. The user can’t interact with cells on hidden sheets — they don’t need validation cleared because they won’t be typing in them
2. Very-hidden sheets often contain macro support data or lookup lists where validation on cells is intentional. Stripping validation from _ValidationLists would break every dropdown that references it
3. Reporting what’s on hidden sheets would confuse the count — “Found validation on 8 sheets” but you only see 5 sheet tabs

If you want to include hidden sheets, change xlSheetVisible to xlSheetHidden and the macro will process hidden sheets and skip only very-hidden ones. See Adapt It.

#Adapt It