VBA to return a list of ranges used by data validation

[Moosles]

I have a fairly sizeable workbook with a lot of named ranges, many of which are probably redundant which I'd like to delete. It's fairly routine to establish if a named range is used in a Formula or in any part of the VBA code within the workbook, but I can't think of a way to work out if a named range is used as the source for any data validation. Obviously I only want to delete named ranges that are not in use by any data validation rules. There is one tab in particular that I'd like to focus on that has ~1500 rows and is made up of 3 main columns that consist of various dropdowns that either look at explicit named ranges, specific regions of other workbooks, or are explicitly written out selections (e.g. Yes, No).

Is there a way to loop trough all cells in this tab to show what the validation ranges are? Ideally I'd like output displayed similarly to the table below. :

Cell Address	Data Validation
A1	None
A2	=NamedRange1
A3	='Lookup Tab'!$S1:$S35
A4	None
A5	Yes, No

In an ideal world, if there is no validation in a cell then maybe the output could skip adding to the table, although this isn't vital. Thanks!

 

[DanteAmor]

The following macro reads the validations from sheet "Sheet1", adjusts that name.
And puts the results on the "Summary" sheet, also adjusts that name.
The results are placed in columns A, B, and if applicable, if you have a validation between two values, the initial value in column B and the final value in column C.

Sub Macro1()
  Dim rng As Range
  Dim i As Long
 
  i = 2
  With Sheets("Summary")
    .Range("B:C").NumberFormat = "@"
    .Range("A2:C" & Rows.Count).ClearContents
    For Each rng In Sheets("Sheet1").Cells.SpecialCells(xlCellTypeAllValidation)
      .Range("A" & i).Value = rng.Address(0, 0)
      .Range("B" & i).Value = rng.Validation.Formula1
      .Range("C" & i).Value = rng.Validation.Formula2
      i = i + 1
    Next
  End With
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Micron]

redacted my post because I'm pretty sure it falls short.