CODEWITHSUNDEEP
excelvba
Joe Patrick
Joe Patrick

Reputation: 51

Excel VBA macro: how do I find cells with list validation

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

Answers (2)

David Zemens
David Zemens

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

CLR
CLR

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

Related Questions