Remove Drop Down data validation list from Excel & VBA

How to Remove DropDown box in Excel & VBA?

If you know the cell address that you are going to delete, then it is easy.

But there are times, where we might want to delete all the validation lists in a Sheet or a Excel Workbook.

Here is the code to do this. Just so much easy – isn’t it?

Sub Delete_Drop_Down_Validation_List()
   'Delete Drop Down in a Range
   Thisworkbook.Range("A1").Validation.Delete

   'Delete All Validation Lists in a Sheet
   Thisworkbook.Cells.Validation.Delete
End Sub

Yes. It does not need to be done with Loop each Dropdown. One line of Code does it all.

We have used the Selection.Validation option in Excel Macro to create this dropdown list.

In the .Add property, we have many fields that can be used to create different type of validation on a cell. i.e., we can make the Excel to validate a input entry made on any cell to match the criteria.

Manually Remove DropDown list in a Cell

Follow these steps to delete a data validation drop down list in a Excel Worksheet.

1. Select the cell that has drop down list.
2. Choose Menu -> Data -> Data Validation
3. In Settings Tab, choose “Any Value” for Allow field.
4. Click Ok.

Or you can choose the cell & click on clear all in the Home menu options. This will clear data, formats & if any validations set for the cell.

You can now see that the drop down symbol is removed in the selected cell.