#1
|
|||
|
|||
Loop through cells and return only unique values
Morning,
I need a bit of code to check a range of cells and only return unique values, they contain names. |
#2
|
|||
|
|||
Here's a bit of code that you should be able to adapt
Code:
Sub Uniques_From_Column() Dim lr As Long, i As Long Dim dic As Object Dim arr As Variant With Sheets("Sheet1") lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row arr = .Range("B2:B" & lr) End With Set dic = CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr, 1) dic(arr(i, 1)) = 1 Next i 'write uniques to a column Range("E2").Resize(dic.Count) = Application.Transpose(dic.keys) ' or 'do something with each unique For i = 0 To dic.Count - 1 'do what you want with each 'this prints to the immediate window Debug.Print dic.keys()(i) Next i End Sub Excel VBA Dictionary – A Complete Guide Last edited by NoSparks; 10-11-2018 at 09:42 AM. Reason: add link |
#3
|
|||
|
|||
Thanks, but after looking on the endless web I put together this.
Code:
For Each rcell In Range("L4:L150") If rcell.Value Like "?*@?*.?*" Then next_cell_value = next_cell_value & "; " & rcell If InStr(next_cell_value, rcell) = 0 Then next_cell_value = next_cell_value & rcell & ";" End If Next rcell |
#4
|
|||
|
|||
You might want to double check that.
|
#5
|
|||
|
|||
I see my error, I'm trying to work it out now.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to group table by unique values in PowerPivot | rlf793 | Excel | 0 | 05-17-2017 05:23 PM |
Count Duplicate Values without a specific Unique Value | Brittni | Excel | 1 | 02-01-2017 06:22 PM |
Count unique values that match 2 or more criteria | caeiro01 | Excel | 1 | 10-25-2015 02:34 AM |
Combining Records of two sheets with Loop till Unique ID | abhilashv | Mail Merge | 1 | 01-22-2014 04:02 AM |
Display unique values and count the number of child items | vthomeschoolmom | Excel | 2 | 07-25-2013 06:17 AM |