VBA: need unique values from a column

kweaver

Well-known Member
Joined
May 12, 2009
Messages
2,934
Office Version
  1. 365
  2. 2010
In a worksheet (called Summary) I have values from G2 to Gn and I'd like column H from H2 to however long to be the unique values from G.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Would this be the best way?

Code:
Range("G2:G" & LRsum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H2"), Unique:=True
 
Upvote 0
Try

Code:
Sub MM1()
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("H2"), Unique:=True
End Sub
 
Upvote 0
For some reason (I don't know why), I had a repeat of the smallest value in the sorted list of the G column that got placed in the H column.

Maybe my explanation wasn't correctly worded. I want only 1 occurrence of each value in the H column.
 
Last edited:
Upvote 0
Try this.

Code:
Sub UNIQUE()
Dim AR() As Variant: AR = Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).Value

With CreateObject("System.Collections.ArrayList")

For i = 1 To UBound(AR)
    If Not .Contains(AR(i, 1)) Then .Add AR(i, 1)
Next i

.Sort
Range("H2").Resize(.Count, 1).Value = Application.Transpose(.toArray)

End With
End Sub
 
Last edited:
Upvote 0
Testing Michael's solution, I think the problem is that it is expecting a header row. So, it's just going to repeat whatever is at the top of the range. Easy enough fix, just add a header column and change the ranges from G2 and H2 to G1 and H1.

Testing on 100,000 records, Michael's is a lot faster than mine as well. Mine took 0.62 seconds and his only took 0.03 seconds. So about 20x faster.

Can't really time it, but Power Query is a pretty fast option as well.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveDupes = Table.Distinct(Source),
    Sort = Table.Sort(RemoveDupes,{{"Column1", Order.Ascending}})
in
    Sort
 
Upvote 0
Thanks to both of you. I put a header in G1 and H1 (same header in each) and if I F8 my way through it using this:

Code:
Sheets("Summary").Range("G1:G" & LRsum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("H1:H" & LRsum), Unique:=True

it works. But, when I don't stop the execution and try to have it run through without my stepping through, I get an "extract range has a missing..." error.

EDIT: ah ha...found it. I needed to remove "ActiveSheet" from the above code and specifically reference the Summary sheet. Phew! Thanks again!!!
 
Last edited:
Upvote 0
Code:
Sheets("Summary").Range("G1:G" & LRsum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("H1:H" & LRsum), Unique:=True

EDIT: ah ha...found it. I needed to remove "ActiveSheet" from the above code and specifically reference the Summary sheet. Phew! Thanks again!!!
FWIW you only need to specify the first cell of the CopyTo range
Code:
CopyToRange:=Sheets("Summary").Range("H1")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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
Back
Top