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

Try replacing:

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

r2 should still pickup the Validation cells.
--
Gary''s Student - gsnu200802


"Mifty" wrote:

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

Fantastic !!!!

This has made my day so much happier

Thank you so much Gary's Student :-)
--
Mifty


"Gary''s Student" wrote:

Try replacing:

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

r2 should still pickup the Validation cells.
--
Gary''s Student - gsnu200802


"Mifty" wrote:

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

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

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

--
Mifty


"Mike Fogleman" wrote:

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