Show Cells that are referenced in data validation

[mrkris1982]

I took over a spreadsheet for someone and I want to clean it up. On sheet1, there are several places where list validation is applied that references lists on sheet2. I am pretty sure some of the lists on sheet2 are NOT being referenced, so I want to get rid of those items for the sake of keeping a clean workbook. Problem is, I am not sure how this can be determined other than manually checking....any suggestions?

 

[DanteAmor]

If on sheet2 you have a name that is not used on sheet1, then we can delete the data on sheet2 and also delete the name of the book.

Run the next macro:

Sub Verify_Validation()
'Show Cells that are referenced in data validation
    Sheets("sheet1").Select
    For Each nombre In ActiveWorkbook.Names
        wname = nombre.Name
        existe = False
        For Each vali In ActiveCell.SpecialCells(xlCellTypeAllValidation)
            wtype = vali.Validation.Type
            If wtype = 3 Then
                wformula = vali.Validation.Formula1
                wrefer = Mid(wformula, 2)
                If wname = wrefer Then
                    existe = True
                End If
            End If
        Next
        If existe = False Then
            On Error Resume Next
            rango = Range(wname).Address
            hoja = Range(wname).Worksheet.Name
            If LCase(hoja) = LCase("sheet2") Then
                Sheets("sheet2").Range(rango).ClearContents 'clean data
                ActiveWorkbook.Names(wname).Delete          'delete name
            End If
            On Error GoTo 0
        End If
    Next
    MsgBox "End"
End Sub

Regards

 

[Akuini]

I took over a spreadsheet for someone and I want to clean it up. On sheet1, there are several places where list validation is applied that references lists on sheet2. I am pretty sure some of the lists on sheet2 are NOT being referenced, so I want to get rid of those items for the sake of keeping a clean workbook. Problem is, I am not sure how this can be determined other than manually checking....any suggestions?

Another option:
Try this on a copy of your workbook:
This code will highlight the lists on sheet2 are being referenced, so you can remove the ones that aren't highlighted.

Sub a1080905a()
Dim r As Range, tx As String

For Each r In Range("A1").CurrentRegion.SpecialCells(xlCellTypeAllValidation)
    tx = r.Validation.Formula1
    tx = Right(tx, Len(tx) - 1)
        On Error Resume Next
        Evaluate(tx).Interior.Color = vbYellow
        On Error GoTo 0
Next
End Sub