Hi everyone,

I'm trying to clear data validation from a range of cells in all the sheets
of a workbook.

I've found various bits of code that are doing the same sort of thing as I
need to do and Bob Phillips also helped
http://www.microsoft.com/office/comm...&lang=en&cr=US

I should have perservered at the time Bob Phillips was helping. I was taking
too long with a project so to get it finished I just recorded the whole lot,
selecting each cell and replacing contents with the ="". Of course it took
ages but seemed to work then it wouldn't. So I still haven't finished the
project and look silly because I said it was nearly there!

So I've played around and come up with this but it's not detecting the data
validation cells I get the msgbox saying No Data Validation Cells.

If anyone could help I would be more than grateful!

Option Explicit
Sub Datavalreset()
Dim rng2 As Range ' specifies range in wksh
Dim rng As Range 'specifies type of cell i.e. special cells
Dim ws As Worksheet
Dim cell As Range

For Each ws In ActiveWorkbook.Worksheets

On Error Resume Next
Set rng = Range("A1:T45")
Set rng2 = rng.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng2 Is Nothing Then

For Each cell In rng2

If cell.Validation.Type = xlValidateList Then
cell.Value = ""
End If
Next
Else
MsgBox "No Data Validation Cells"
End If
Next ws
End Sub

Many thanks
--
Mifty