Finding Unique Values in VB

Too$hort

New Member
Joined
Jun 27, 2005
Messages
10
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
ABCDEF
1DataUniqueValues
2bbaa
3bbbb
4cc
5ccdd
6zzff
7cczz
8zz
9
10aa
11ff
12dd
13cc
14
15
16
Sheet1
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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.

Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
you can use AdvancedFilter to get unique record(s)
Code:
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
 

Forum statistics

Threads
1,136,988
Messages
5,678,970
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top