Unique list from multiple columns with advanced filter

[juanbolas]

Hello

I'm trying to get a unique list of names using the advanced filter but my code is just showing the first unique item.

Can someone help me please?

Thanks in advance

Sub Proveedores_UniqueList()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long

Set wsSource = ThisWorkbook.Sheets("DB_PDP").Range("PDP_ProveedoresRange")
Set wsTarget = ThisWorkbook.Sheets("Proveedores_UniqueList")

Set rngTopCell = wsSource.Offset(1, 0)
Set rngBottomCell = wsSource.Offset(wsSource.CurrentRegion.Rows.Count - 1, 0)
Set rngEntireRange = Range(rngTopCell, rngBottomCell)

rngEntireRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsTarget.Range("A:A"), Unique:=True

End Sub

 

[Peter_SSs]

You did not address these questions

Expected results: I assume that Prov 34 is an error?

Does this have to be vba? What about this formula solution?

juanbolas.xlsm
	A
1	Prov 1
2	Prov 2
3	Prov 3
4	Prov 4
5	Prov 6
6	Prov 7
7	Prov 9
8	Prov 10
9	Prov 11
10	Prov 12
11	Prov 13
12	Prov 14
13	Prov 15
14	Prov 16
15	Prov 17
16	Prov 18
17	Prov 19
18	Prov 5
19
Proveedores_UniqueList

Cell Formulas
Range	Formula
A1:A18	A1	=LET(a,DB_PDP!X11:Z100,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),UNIQUE(FILTER(arr,arr<>"")))
Dynamic array formulas.

 

[juanbolas]

The range is actually x11:z11

The range is actually x11:z11

No, it's just X11:Z11 the headers for each of the three columns

 

[juanbolas]

You did not address these questions

Sorry. Prov 34 is NOT an error; it's one of the data items. And yes, it has to be in VBA Thanks in advance

 

[Peter_SSs]

Prov 34 is NOT an error; it's one of the data items.

Hmm again. That value was included in your response to

What is your expected result for that sample data?

I am unable to find Prov 34 anywhere in the sample data in post #6 sample data (or in post #1)

No, it's just X11:Z11 the headers for each of the three columns

Again looking at the sample data in post #6, if it is the headers, then surely it is X10:Z10?

The first thing that I usually do to try to help in the forum is to set up a sample worksheet like yours and then try to produce the results you say you want. That is difficult if the information being provided is wrong or confusing. To get the fastest and best help, please try to give careful & accurate information.

So, for the suggestion below, I am assuming
- that Prov 34 is an error in the expected results for the sample data provided in post 6
- that the names range "PDP_ProveedoresRange" is X10:Z10 for the sample data provided in post 6

Try this with a copy of your workbook.
(I have not used Advanced Filter as you requested because that will not produce a single column unique list from a multi-column range.)

Sub Proveedores_UniqueList_v2()
  Dim wsSource As Worksheet, wsTarget As Worksheet
  Dim d As Object
  Dim a As Variant
  Dim rws As Long, i As Long, j As Long

  Set wsSource = ThisWorkbook.Sheets("DB_PDP")
  Set wsTarget = ThisWorkbook.Sheets("Proveedores_UniqueList")
  Set d = CreateObject("Scripting.Dictionary")
 
  With wsSource.Range("PDP_ProveedoresRange")
    rws = .EntireColumn.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - .Row
    a = .Offset(1).Resize(rws).Value
  End With
  For j = 1 To UBound(a, 2)
    For i = 1 To UBound(a, 1)
      If Not IsEmpty(a(i, j)) Then d(a(i, j)) = 1
    Next i
  Next j
 
  wsTarget.Range("A1").Resize(d.Count).Value = Application.Transpose(d.Keys)
End Sub

For the sample data in post 6, this is the result of the above code

juanbolas.xlsm
	A
1	Prov 1
2	Prov 2
3	Prov 3
4	Prov 4
5	Prov 6
6	Prov 7
7	Prov 9
8	Prov 10
9	Prov 11
10	Prov 12
11	Prov 13
12	Prov 14
13	Prov 15
14	Prov 16
15	Prov 17
16	Prov 18
17	Prov 19
18	Prov 5
19
Proveedores_UniqueList