Excel Macro: Removing Data Validations from Active Workbook

Unhiding a lot of sheets is annoying task. So, I’ve made this Excel VBA macro.
This macro unhides all the worksheets in an active workbook.

Macro Example

Sub RemoveDataValidations_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.Validation.Delete 
  Next sh

End Sub

Description

Variable Declaration

The 3rd line declares an object variable called sh which will refer to a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Starts Looping

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets

The 5th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

[Application.]Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

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

obj-Excel-Worksheets

Removes Data Validation

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.Validation.Delete 

The 6th line removes the data-validation.

Worksheet.Cells

    sh.Cells...

The Worksheet.Cells property returns the Range object represents ALL ranges on the worksheet.

obj-Excel-Worksheet-Cells

Range.Validation

    sh.Cells.Validation...

The Range.Validation property returns the Validation object.

obj-Excel-Range-Validation

Validation.Delete

    sh.Cells.Validation.Delete 

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

obj-Excel-Validation-Delete

Loops Back

The 7th line loops back to get the next worksheet.

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Visible = xlSheetVisible 
  Next sh

After every worksheet is evaluated, this macro ends.

Sub RemoveDataValidations_ActiveBook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.Cells.Validation.Delete 
  Next sh

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007