Remove all data validation from a cell in Excel with this free Excel macro. This is a great macro to use when you need to remove all data validation from cells in Excel. This removes drop down menus and lists, input messages, and all other data validation from a cell in Excel.
Below, there are two versions of this macro. The first version works on a specific hard-coded cell or range of cells in Excel while the second macro will work on any selected cell or range of cells within Excel.
To use the first macro, replace A1 with the cell reference or range of cells from which you want to remove data validation. The second macro does not need to be changed; simply select any cell or range of cells in Excel and run the second macro and it will work.
Where to install the macro: Module
Excel Macro to Remove All Data Validation from a Specific Cell or Range of Cells in Excel
Sub Remove_Data_Validation_Cell[] Range["A1"].Validation.Delete End SubExcel Macro to Remove All Data Validation from a Selection of Cells in Excel
Sub Remove_Data_Validation_Selection[] Selection.Validation.Delete End Sub
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. [No VBA experience required.]
View Course
Similar Content on TeachExcel
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions. Or For other ways to get there, Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject [YOUR FILE'S NAME HERE] and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook,
Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to
Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
See all How-To Articles This tutorial will demonstrate how to remove a drop-down list in Excel and Google Sheets.
If you have a drop-down list in Excel that is created using Data Validation, you may wish to remove it to allow the user to enter any information into the cell rather than what is just contained in the drop-down list.
Removing Data Validation
- To remove Data Validation, first select the cell that contains the drop-down list.
- Then, in the Ribbon, select Data > Data Tools > Data Validation.
- In the Settings tab, click Clear All, and then click OK.
The drop-down list is removed from the selected cell.
Remove a Drop-Down List in Google Sheets
- Select the cell in Google Sheets that has the drop-down list in it.
- In the Menu, select Data validation.
- Click Remove validation.
The drop-down list is removed from the selected cell.