Finding Unique Values in VB

[Too$hort]

hello,

Could someone please assist how to extract unique values from a certain column into another column. I want to "exclude" any blanks and also have it sorted alphabetically if its all letters or sorting in ascending order if they are numbers.

I know that you can somewhat do this with "advanced filter" but I would like it done in VB in a macro.

Any assistance would be great. I have done a search but i cannot find anything specific to what i require.

here is a screen shot of what i need. Column A has the data. I want to end up with what is in Column D.

Thanks in advance.

Book16.xls
	A	B	C	D	E	F
1	Data			UniqueValues
2	bb			aa
3	bb			bb
4				cc
5	cc			dd
6	zz			ff
7	cc			zz
8	zz
9
10	aa
11	ff
12	dd
13	cc
14
15
16
Sheet1

 

[venkat1926]

the data is in column A.try this macro on an experimenal sheet same as you have given in the posting.

the macro sorts column A so that blank rows go down and use some code staements to get unique vaues.

If it is ok you can use it for your data.

Sub uniquevalues()
Dim rng, c, dest As Range
Set rng = Range(Cells(2, "a"), Cells(Rows.Count, "a").End(xlUp))
rng.Sort key1:=Range("a1"), header:=xlNo
Set dest = Range("d1")
dest = "data"
Set dest = dest.Offset(1, 0)
For Each c In rng
If c <> c.Offset(-1, 0) Then
c.Copy dest
Set dest = dest.Offset(1, 0)
End If
Next

End Sub

 

[jindon]

Hi
you can use AdvancedFilter to get unique record(s)

Sub test()
With Range("a1",Range("a" & Rows.Count).End(xlUp))
   .AdvancedFilter action:=xlFilterCopy,CopyToRange = Range("d1"),unique:=True
End With
Range("d1").CurrentRegion.Sort key1:=Range("d1"),order1:=xlascending,header:=xlYes
End Sub