Extract unique values from multiple columns in one row

[Grapez]

Hi,

I have a sheet with 15000 rows and in column AV-CK have values which can contain duplicates.

I'm trying to get in CL the unique values from AV-CK (merged / concatenated)

Everything I've found so far (advanced filter, autofilter, remove duplicates) work on a range of values in one column.....but I have them in a row.

Any suggestions on how to best do this? It's basically a concatenate of unique values.

Thanks,
Henk-Jan

 

[DanteAmor]

Try this

Sub Extract_unique_values()
  Dim r As Range, a() As Variant, i As Long, j As Long, dict As Object
  Set r = Range("AV1:CK" & Range("AV" & Rows.Count).End(xlUp).Row)
  Set dict = CreateObject("scripting.dictionary")
  a = r.Value
  For i = 1 To UBound(a)
    For j = 1 To r.Columns.Count
      dict(a(i, j)) = dict(a(i, j))
    Next
  Next
  Range("CL1").Resize(dict.Count) = Application.Transpose(dict.Keys)
End Sub