Excel Macro: Removing Data Validations from an Active Sheet

This Excel VBA macro removes all the data-validations from an active worksheet.

Macro Example

Sub RemoveDataValidations_ActiveSheet()

  Cells.Validation.Delete

End Sub

Description

We need only just following code:

  Cells.Validation.Delete

The Application.Cells property returns the Range object represents ALL ranges on the active sheet.
So we don’t have to loop through.

obj-Excel-Application-Cells

The Cells property is a member of the Excel.Global class, so we can omit the Application property, .

obj-Excel-Cells

The Range.Validation property returns the Validation object.

obj-Excel-Range-Validation

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

obj-Excel-Validation-Delete

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007