Putting named range into sub written by Tom Olgivy DataVal

[Mifty]

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

 

[Gary''s Student]

Try replacing:

Set r = Range("B1").EntireColumn
with:
Set r = Range("B3:F11")

r2 should still pickup the Validation cells.

 

[Mike Fogleman]

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]

Fantastic !!!!

This has made my day so much happier

Thank you so much Gary's Student

 

[Mifty]

Hi Mike,

Thanks for replying. I was so chuffed that I got it to work that I didn't
check out any other replies.

I haven't changed R1 or R2 and it's still working - so even though I'd love
to know why - I'm going with the 'If it ain't broke........

Cheers