Count unique values in multiple range

JPGraphX

Board Regular
Joined
Aug 6, 2012
Messages
74
Hello,

What I'm trying to do is to count the number of unique values in multiple range on a sheet. I've found a solution on your forum that do it.
With list.count I can get the number of unique value but I've tried a lot of thing to get those value and I'm not able to do it.

If list.count returns 5, I would like to be able to get the 5 different value.

VBA Code:
Private Sub CommandButton2_Click()
Dim Rng As Range, List As Object
Set List = CreateObject("Scripting.Dictionary")


For Each Rng In Range("G5:G18")
If Rng.Value <> "" Then
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
End If
Next

For Each Rng In Range("G22:G28")
If Rng.Value <> "" Then
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
End If
Next

For Each Rng In Range("G32:G37")
If Rng.Value <> "" Then
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
End If
Next

For Each Rng In Range("G42:G48")
If Rng.Value <> "" Then
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
End If
Next

For Each Rng In Range("G52:G61")
If Rng.Value <> "" Then
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
End If
Next

End Sub


Thanks!!
JP
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JPGraphX

Board Regular
Joined
Aug 6, 2012
Messages
74
With that example, I need to retrieve those value:

G. Champagne
Équipe D
R. Etcheverry
 

Attachments

  • 2021_05_10_09_10_10_SUIVI_CEDULE_PREVENTIF_2021.xlsm_Enregistré.png
    2021_05_10_09_10_10_SUIVI_CEDULE_PREVENTIF_2021.xlsm_Enregistré.png
    37.1 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,242
Office Version
  1. 365
Platform
  1. Windows
Where do you want to put the values?
 

JPGraphX

Board Regular
Joined
Aug 6, 2012
Messages
74
My goal is to print multiple copy (list.count number of copy) of my sheet and change a cell value with each different value. (with a For - Next)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,242
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case you can add something like this to the end of your code
VBA Code:
If List.Count > 0 Then
   For i = 0 To List.Count - 1
      Range("A2").Value = List.Keys()(i)
      'do something
   Next i
End If
 
Solution

JPGraphX

Board Regular
Joined
Aug 6, 2012
Messages
74
Wow, I've search hours on web to do exactly this. I was trying List.Keys(i)...
Thanks so much!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,242
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,392
Messages
5,769,807
Members
425,573
Latest member
AmroK

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