How To Find Which Cells contain data validation

[thewiseguy]

Hi all - I am trying to run through the many tables I have created in my workbook and see which ones are no longer being used. Is there a way to do this?

Thanks in advance!

 

[Tupe77]

Do you mean something like this?

I think this is the answer to the title, but I doubt that this is the answer to the problem of the question itself?

This selects all cells in the active worksheet that have a Data Validation Rule.
When they are selected, you can, for example, define a fill color for them.

Sub TS_FindDataValidations()
Dim RangeToTest As Range: Set RangeToTest = ActiveSheet.UsedRange
Dim iC As Variant, DataValidationType As Variant
Dim CellsWithValidation As Range

For Each iC In RangeToTest.Cells
    DataValidationType = Null                               ' Clearing the Datavalidation type for next cell
        On Error Resume Next                                ' If no validation in cell then error, so skip errors
            DataValidationType = iC.Validation.Type         ' Read the possible Data Validation type
        On Error GoTo 0                                     ' Back to normal error handling.
       
        If Not IsNull(DataValidationType) Then              ' Checking if the Datavalidation type has been read from the cell.
            If CellsWithValidation Is Nothing Then          ' Check if the CellsWithValidation area is already created
                Set CellsWithValidation = iC                ' Will be created if still empty
            Else
                Set CellsWithValidation = Union(CellsWithValidation, iC) ' Add a Cell with a Datavalidation Rule to the CellsWithValidation range.
            End If
        End If
Next iC

CellsWithValidation.Select ' Selecting all cells containing Data Validation rules.
End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[RoryA]

ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation).Select

should be sufficient?

 

[Tupe77]

ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation).Select

should be sufficient?

Clearly it is sufficient.