Dear Forum,
In the attached file, sheet 1 contains two columns (ref1 & ref2) with names.
I want on sheet 2, column A have a list with unique names.
After much googling, I came up with using advanced filters
Code
Sub test1()
Dim ws1 As Worksheet, ws2 As Worksheet, lr1 As Long, lr2 As Long, er1 As Long
Dim rng1 As Range, rng2 As Range, rngList As Range, rngCopy As Range
Set ws1 = Sheet1
Set ws2 = Sheet2
lr1 = ws1.Cells(Rows.Count, 4).End(xlUp).Row
er1 = ws1.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
Set rng1 = ws1.Range(ws1.Cells(1, 4), ws1.Cells(lr1, 4))
Set rng2 = ws1.Range(ws1.Cells(2, 5), ws1.Cells(lr1, 5))
With rng1
.Copy Destination:=ws1.Cells(1, 8)
End With
With rng2
.Copy Destination:=ws1.Cells(er1, 8)
End With
lr2 = ws1.Cells(Rows.Count, 8).End(xlUp).Row
Set rngCopy = ws2.Cells(1, 1)
Set rngList = ws1.Range(ws1.Cells(1, 8), ws1.Cells(lr2, 8))
rngList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rngCopy, Unique:=True
With ws2
.Cells(1, 1) = "Lista med Domare"
.Columns.AutoFit
End With
End Sub
Display More
My questions are:
1) If I understand correctly, I can´t remove the list range on which advanced filter is based?
2) I want hide help column (ie column - how do I do that?
3) Do you have any better solution without using Array?
[ATTACH]n1209284[/ATTACH]
Thanks in advance
Regards
Peter