Reputation: 51
I'd like a macro to clear all cells in a given range on a worksheet UNLESS it has (specifically) LIST validation. (In that case I want to make it = 'Select'.)
So... I need the macro to:
1)check all cells within a range on the sheet 2)if the cell does NOT have (specifically) LIST validation it will make the cell = "" 3)if the cell DOES have (specifically) LIST validation it will make the cell = 'Select'
Something like this:
Dim x as variant
with thisworkbook.sheets("audits")
For each x in .range("A6:AZ200")
if x.validationtype = "list" then
x.value = "Select"
else
x.value = ""
end if
next x
end with
Thanks!
Upvotes: 0
Views: 4370
Reputation: 53663
You can probably use the SpecialCells
property of a range object to return ONLY the cells with validation, and then do another check to ensure that the validation type is List.
Dim rng As Range
Dim vRng As Range
Dim cl As Range
Set rng = thisworkbook.sheets("audits").Range("A6:AZ200") 'Modify as needed
'Get a range of ONLY the validation cells
Set vRng = rng.SpecialCells(xlCellTypeAllValidation)
For Each cl In rng
'If the cell has NO VALIDATION:
If Intersect(cl, vRng) Is Nothing Then
cl.ClearContents
ElseIf cl.Validation.Type = 3 Then 'xlValidateList
cl.Value = "Select"
End If
Next
Note: 3
is the xlDVType
constant for "List" validation. You could alternatively use the constant expression: xlValidateList
The above should handle mixed validation type, and will do nothing with any other sort of validation. If it's safe to assume that ONLY list validation is used, then try condensing it as:
Set vRng = rng.SpecialCells(xlCellTypeAllValidation)
vRng.Value = "Select"
For Each cl In rng
'If the cell has NO VALIDATION:
If Intersect(cl, vRng) Is Nothing Then
cl.ClearContents
End If
Next
Upvotes: 5
Reputation: 12289
This would be one way to do it. In order to keep the error handling away from your main routine I've put the validation checker into a standalone function:
Sub clear_validation()
Dim x As Range
With ThisWorkbook.Sheets("audits")
For Each x In .Range("A6:AZ200")
If validationtype(x) = 3 Then
x.Value = "Select"
Else
x.Value = ""
End If
Next x
End With
End Sub
Function validationtype(cl As Range)
Dim t As Integer
t = 0
On Error Resume Next
t = cl.Validation.Type
On Error GoTo 0
validationtype = t
End Function
It's flicker-y, so you might want to temporarily turn off screen updating, and perhaps calculations while it's running, but I think this does what you're after.
Upvotes: 1