Compile a list of distinct values from a name range that spans multiple columns

As suggested in the comments (linked) you can use a combination of TEXTJOIN and FILTERXML but the formulas are quite long which impacts readability. Power Query is another option.

Over the years, I used my own VBA function to unite values. You can use the DM_ARRAY function from my repository: VBA-ArrayTools. There is a demo workbook available and you will find a dedicated worksheet for each User Defined Function (UDF). For DM_ARRAY, the first parameter is the number of columns you want as output and then you can pass as many ranges as you need. In your case you could achieve the desired result with =UNIQUE(DM_ARRAY(1,E2:E23,I2:I23,M2:M23)). Of course you can pass a named range instead like =UNIQUE(DM_ARRAY(1,WESupplierALL)).

Moreover, if you want to achieve the same result on a different computer where you don't have the advanced array formulas, then the same library has a DM_UNIQUE function that you can use.

Write Unique Values From Multiple to One Column

• Adjust (play with) the values in the constants section and the workbook.

Option Explicit

Sub writeUnique()
    
    Const wsName As String = "Sheet1"
    Const ColsList As String = "E,I,M"
    Const fRow As Long = 2
    Const lRow As Long = 23
    Const dFirst As String = "A2"
        
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim Cols() As String: Cols = Split(ColsList, ",")
    Dim rCount As Long: rCount = lRow - fRow + 1
    
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' ignore case
      
    Dim crg As Range
    Dim Data As Variant
    Dim Key As Variant
    Dim r As Long, n As Long
    
    For n = 0 To UBound(Cols)
        Set crg = ws.Columns(Cols(n)).Resize(rCount).Offset(fRow - 1)
        Data = crg.Value
        For r = 1 To rCount
            Key = Data(r, 1)
            If Not IsError(Key) Then ' ignore error values
                If Len(Key) > 0 Then ' ignore blanks
                    dict(Key) = Empty
                End If
            End If
        Next r
    Next n
    
    If dict.Count = 0 Then Exit Sub
    
    ReDim Data(1 To dict.Count, 1 To 1)
    r = 0
    
    For Each Key In dict.Keys
        r = r + 1
        Data(r, 1) = Key
    Next Key
    
    With ws.Range(dFirst)
        .Resize(r).Value = Data
        .Resize(.Worksheet.Rows.Count - .Row - r + 1).Offset(r).ClearContents
    End With
    
End Sub