Excel Macro: Removing Data Validations from Selected Cells

This Excel VBA macro removes the data-validations from the selected cells.

Macro Example

Sub RemoveDataValidations_SelectedCells()

  Selection.Validation.Delete

End Sub

Description

We need only just following code:

  Selection.Validation.Delete

[Application.]Selection

  Selection...

The Application.Selection property returns the selected object.

obj-Excel-Application-Selection

If the cells are selected on a worksheet, this property returns the Range object.
And, this Range object represents all the selected cells on the active sheet.
So, we do not need to loop through.
The Selection property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-Selection

Selection.Validation (Range.Validation)

  Selection.Validation...

The Selection.Validation (Range.Validation property) returns the Validation object.

obj-Excel-Range-Validation

Validation.Delete

  Selection.Validation.Delete

And, we can remove data-validations by the Validation.Delete method.

obj-Excel-Validation-Delete

Sub RemoveDataValidations_SelectedCells()

  Selection.Validation.Delete

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-delete-validation.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007