Putting named range into sub written by Tom Olgivy DataVal

M

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
 
G

Gary''s Student

Try replacing:

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

r2 should still pickup the Validation cells.
 
M

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top