Perhaps Set r = Range("RngDV")
I also noted that you Dim r1 As Range but use r2 in the code. Is this a
typo?

Mike F

"Mifty" wrote in message
...
Hi everyone,

I've found this routine written by Tom Olgivy that allows you to reset all
the data validation in a column.

I would like to change it to reset data val cells to blank ="" which is
the
2nd option in the data val.

The data val cells are in non-contiguous rows ( rows 3,5,7,9,11) on
columns
B to F. I've made these into a named range RngDV.

I got stuck very easily because I didn't know how to replace entirecolumn
in
the code below
Sub ResetBox()
Dim r As Range
Dim r1 As Range
Set r = Range("B1").EntireColumn
On Error Resume Next
Set r2 = r.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not r2 Is Nothing Then
For Each cell In r2
If cell.Validation.Type = xlValidateList Then
cell.Value = ""
End If
Next
Else
MsgBox "No Data Validation Cells)"
End If
End Sub

I'd appreciate any help :-)
Thank you
--
Mifty