Remove Data Validation with macro

[Mifty]

Hi there,

I thought that this would be easy, what I want to do is remove Data
Validation from ranges in a number of sheets.

I recorded the macro below - but it won't run - runtime error 1004

Application.Goto Reference:="R1C1"
ActiveCell.SpecialCells(xlCellTypeAllValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween 'BUGS OUT HERE
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Cells.Select
Range("B5").Activate
Selection.EntireRow.Hidden = False
Range("H1:H4").Select
Selection.ClearContents
Rows("1:4").Select
Range("B1").Activate
Selection.EntireRow.Hidden = True

Any help would be appreciated

Thank you

 

[Bob Phillips]

This works fine for me

For Each ws In ActiveWorkbook.Worksheets

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng Is Nothing Then

For Each cell In rng

With cell.Validation
.Delete
.Add Type:=xlValidateInputOnly,
AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween 'BUGS OUT HERE
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Next cell
End If
Next ws

 

[Mifty]

Hi Bob,

Thanks for responding

I'm not sure what I'm doing wrong but I'm still getting a problem
I get a compile error - syntax error now when I try to debug. same bit of code

Cheers

 

[Bob Phillips]

Maybe wrap-around

For Each ws In ActiveWorkbook.Worksheets

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng Is Nothing Then

For Each cell In rng

With cell.Validation
.Delete
.Add Type:=xlValidateInputOnly, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Next cell
End If
Next ws

 

[Mifty]

Hi Bob,

I'm going to have to do it the old fashioned way sheet by sheet - but I'll
come back to this when I have some time to look at it properly. It WILL work

Thanks for all your help. I'll repost at the sage we left off

Cheers