Getting unique entries from an array/column

[erikhs]

Hi,

I would like to make an array of unique entries from another array or
column(preferably a dynamic range), and then paste back to the
worksheet on a different sheet. How do i do this with the least
possible code?

 

[Bob Phillips]

Sub CopyData()
Columns("B:B").Insert
Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), _
Unique:=True
Columns("B:B").Copy Sheets("Sheet2").Range("A1")
Columns("B:B").Delete Shift:=xlToLeft
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

If you have a header in that column:
Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=worksheets("Sheet2").Range("A1"), _
Unique:=True

If not, temporarily put a header there, then remove it after your done.
(using code).

 

[Guest]

Just to add to Bob's excellent advice:
Bob appears to be under the mistaken impression that advancedfilter can't
copy to another sheet. That is incorrect. It can. Use of the dummy column
B is not required.

Sub CopyData()
Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2") _
.Range("A1"), _
Unique:=True
End Sub


--
Regards,
Tom Ogilvy

Sub CopyData()
Columns("B:B").Insert
Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), _
Unique:=True
Columns("B:B").Copy Sheets("Sheet2").Range("A1")
Columns("B:B").Delete Shift:=xlToLeft
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I certainly was. Looking at it, that seems to be correct for filtering in
Excel, I mistakenly assumed that would also apply to filtering via VBA. I'll
file that away.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

You can do the same in Excel. You just have to start at the destination cell
when you start the advanced filter and it may balk, by saying it can't
determime the data, but continuing on and it works. It is cleaner in later
versions, but I know for sure it works in xl97 and later and probably earlier
too.

 

[Bob Phillips]

It works fine in XP. Thanks for that, I didn't know.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)