Clear cells that are dependents and have validation lists

Question

I need some help. I'm setting up a spread sheet that has multiple validation lists. Each validation list has multiple validation lists linked to them (dependents). When I change the first validation list I need to clear all cells that are dependent on it (if they have a validation list attached).

I'm trying to create a macro that is dynamic enough so I don't need to name ranges (I have too many to do that any way). Basically if the activecell changes / it has a validation list and has dependents - then clear the content of the dependent cell if they have validation lists in them. In principal it sounds straight forward but it's killing me...!!!! Does anyone have such a macro already in place or I would be grateful for any advise? Thanks Ciaran.

Principal Ideas.

Use a dynamic range byval target as range

If the cell has dependents activecell.dependents.count>0

If the actual cell has a validation list etc. activecell.specialcells(xlcelltypeallvalidation)

Answer 1

The following will only look within the current worksheet but could be extended to loop through all worksheets. It also only checks for Validations of type List.

Sub ClearListValidations()
    Dim ws As Worksheet    'not used currently
    Dim rngCurrent As Range
    Dim rngValids As Range
    Dim strLookup As String
    Dim rng As Range
    Dim varIntersect As Variant

    Application.ScreenUpdating = False
    Set rngCurrent = ActiveCell
    On Error Resume Next
    Set rngValids = rngCurrent.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0     'reinstate error handling
    If rngValids Is Nothing Then Exit Sub   'no list validations

    For Each rng In rngValids
        If rng.Validation.Type = 3 Then     'list validation
            Set varIntersect = Application.Intersect(rngCurrent, _
                Range(Mid(rng.Validation.Formula1, 2)))
            If Not varIntersect Is Nothing Then
                rng.ClearContents
                rng.Validation.Delete
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Essentially, if the List-Validation's source intersects with the current cell, it removes the content and validation settings from the cells.

The MID() function is used because Formula1 includes an equals-sign, and we need to discard this to form a Range.