Excel Macro to Remove Data Validation (Selection, Sheet or Workbook)

These VBA macros remove data validation, including drop-down lists and input rules, from the selected cells, the whole sheet, or every sheet in the workbook. The cell values stay as they are.

Works inExcel 2010 and later, Windows and Mac

UndoNot possible. Save a copy first

Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Table of Contents

Remove from the selected cells

Sub RemoveValidationSelection()
    Selection.Validation.Delete
End Sub

Remove from the whole sheet

Sub RemoveValidationSheet()
    ActiveSheet.Cells.Validation.Delete
End Sub

Remove from every sheet

Sub RemoveValidationAllSheets()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In Worksheets
        ws.Cells.Validation.Delete
    Next ws
    On Error GoTo 0
End Sub

On Error Resume Next skips protected sheets instead of stopping.

Find cells with validation first

To see which cells have validation before removing it:

Go to Home → Find & Select → Data Validation.
Excel selects every cell on the sheet that has a validation rule.

Without a macro

Select the cells, or press Ctrl + A for the whole sheet.
Go to Data → Data Validation.
Click Clear All, then OK.

Things to know

Only the rules are removed. The values already in the cells don’t change.
Protected sheets must be unprotected first.
To create drop-down lists that depend on each other, see dependent drop-down lists.