A friendly tip, if you describe your entire project at the start, instead of these bit-by-bit requests along the way, it will take less time and effort to get a final resolution because the suggested programming approaches usually depend on the big picture. In the future just lay out the entire circumstances involved to help you and the people helping you.
Now then, with numerous named ranges, some of which might or might not actually be bona fide named ranges on the sheet you are trying to extract unique cell values from, do this:
Insert a new worksheet and name it "ZZZ" (without the quotes).
In new sheet ZZZ in column A starting in cell A1, list the all the named ranges you might ever have established on the worksheet of interest. Example, in sheet ZZZ, cell A1 contains Table1, cell A2 contains Table2, cell A3 contains Table3, etc. Do this even though, maybe, Table2 is currently not an active named range on this main worksheet you are working with.
Do not skip rows when you list those potential names on sheet ZZZ.
If you feel like it, go ahead and hide sheet ZZZ when you are done listing all potential named ranges.
Regarding the header values of all those named ranges which you do NOT want listed among the unique cell values, I assume those headers occupy the first row of their respective named ranges. Example, if range name Table2 occupies range B6:J10, then B6:J6 should include the field headers for named range Table2. If this is not the case, then make it be the case, which is good design practice for tables and databases.
Recall, the macro I first posted was based on the inference that column J was the last (rightmost) column where named ranges are involved, meaning that columns K, L, and M were empty, further meaning that column L was not touching any data or any named range addresses. If that is still the case, the below macro will do what you want, which is, only list in column L the unique values below the first header row from all recognized named ranges. Then the list in column L will be sorted alphabetically and autofitted for width.
Code:
Sub Test3()
Application.ScreenUpdating = False
Dim RangeCell As Range, cell As Range
Dim x&, varCell As Variant
x = 2: Columns(12).Clear: Err.Clear
Range("L1").Value = "Unique entries in Tables"
For Each RangeCell In Sheets("ZZZ").Range("A1").CurrentRegion
On Error Resume Next
If Not ActiveWorkbook.Names(RangeCell.Value) Is Nothing Then
If Err.Number <> 0 Then
Err.Clear
Else
With Range(RangeCell.Value)
For Each cell In .Offset(1).Resize(.Rows.Count - 1)
If Len(cell.Value) > 0 Then
varCell = Application.Match(cell.Value, Columns(12), 0)
If IsError(varCell) Then
Err.Clear
Cells(x, 12).Value = cell.Value
x = x + 1
End If
End If
Next cell
End With
End If
End If
Next RangeCell
Range("L1").CurrentRegion.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
Columns(12).AutoFit
Application.ScreenUpdating = True
End Sub
