Pop Up Unique Values In Dialogue Box macro

[nirvehex]

Hi all,

I'm trying to write some Excel VBA code that when run, displays in a dialogue box, the unique # of values that I currently have selected. This should exclude things not highlighted especially when I am filtered.

Below is the code I have tried but doesn't work right:

<code>
Sub CountUniqueHighlightedCells()
Dim selectedRange As Range
Dim cell As Range
Dim uniqueValues As New Collection
Dim value As Variant
Dim count As Integer

' Check if any cells are selected
If Selection.Cells.Count = 0 Then
MsgBox "No cells selected!", vbExclamation
Exit Sub
End If

' Loop through each selected cell
For Each cell In Selection
' Check if the cell has a background color
If Not cell.Interior.ColorIndex = xlNone Then
' Add cell value to the collection if it's not already there
On Error Resume Next
uniqueValues.Add cell.Value, CStr(cell.Value)
On Error GoTo 0
End If
Next cell

' Count the number of unique values
count = uniqueValues.Count

' Display the count in a dialogue box
MsgBox "Number of unique values in highlighted cells: " & count, vbInformation
End Sub

</code>

Thank you!

Mark

 

[Jeffrey Mahoney]

Please try this:

Sub CountUniqueHighlightedCells()
  Dim selectedRange As Range
  Dim cell As Range
  Dim value As Variant
  Dim count As Integer
  Dim u As Range
  Dim Ary As Variant
  
  ' Check if any cells are selected
  If Selection.Cells.count = 0 Then
    MsgBox "No cells selected!", vbExclamation
    Exit Sub
  End If
  
  ' Loop through each selected cell
  For Each cell In Selection
    ' Check if the cell has a background color
    If Not cell.Interior.ColorIndex = xlNone Then
      If Not u Is Nothing Then
        Set u = Union(u, cell)
      Else
        Set u = cell
      End If
    End If
  Next cell
  If Not u Is Nothing Then
    Ary = Application.Unique(u)
    count = UBound(Ary)
  End If
  
  ' Display the count in a dialogue box
  MsgBox "Number of unique values in highlighted cells: " & count, vbInformation
End Sub

 

[nirvehex]

Hi there - thanks for your help but this returns a value of 0 when I run it on a highlighted range that should be 10. Any other ideas?

 

[Jeffrey Mahoney]

I ran it several times and tested it and it worked fine for me

 

[Akuini]

This should exclude things not highlighted especially when I am filtered.

1. What do you mean by highlighted?
2. Do you want count only cells that are highlighted & visible?

Below is the code I have tried but doesn't work right:

3. Could you explain in more detailed?

 

[nirvehex]

1. What do you mean by highlighted?
2. Do you want count only cells that are highlighted & visible?

3. Could you explain in more detailed?

Hi there - I select a range of cells could be filtered on not filtered, by highlighting them with my mouse. I would like a unique count of only what's visible and highlighted in the range. I'm not sure why Jeffrey's code is not working for me.

 

[nirvehex]

I ran it several times and tested it and it worked fine for me

Hi Jeffrey - I'm not sure why it wouldn't be working. Here's an example of where it only returned a "1" for me. It should return "3."

 

[Akuini]

Hi there - I select a range of cells could be filtered on not filtered, by highlighting them with my mouse. I would like a unique count of only what's visible and highlighted in the range.

1. So, it has nothing to do with cell's background color, right?
2. Don't use reserved keyword as variable name, such as value, count etc.
3. To get unique count, you can use dictionary object.

Try:

Sub CountUniqueHighlightedCells()
Dim selectedRange As Range
Dim my_Cell As Range
Dim my_Value As Variant

' Check if any cells are selected
If Selection.Cells.CountLarge = 0 Then
MsgBox "No cells selected!", vbExclamation
Exit Sub
End If

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
' Loop through each selected cell
For Each my_Cell In Selection
    If my_Cell.EntireRow.Hidden = False Then  'if the cell is not hidden
        d(my_Cell.value) = Empty
    End If
Next

' Display the count in a dialogue box
MsgBox "Number of unique values in highlighted cells: " & d.count, vbInformation
End Sub

 

[nirvehex]

1. So, it has nothing to do with cell's background color, right?
2. Don't use reserved keyword as variable name, such as value, count etc.
3. To get unique count, you can use dictionary object.

Try:

Sub CountUniqueHighlightedCells()
Dim selectedRange As Range
Dim my_Cell As Range
Dim my_Value As Variant

' Check if any cells are selected
If Selection.Cells.CountLarge = 0 Then
MsgBox "No cells selected!", vbExclamation
Exit Sub
End If

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
' Loop through each selected cell
For Each my_Cell In Selection
    If my_Cell.EntireRow.Hidden = False Then  'if the cell is not hidden
        d(my_Cell.value) = Empty
    End If
Next

' Display the count in a dialogue box
MsgBox "Number of unique values in highlighted cells: " & d.count, vbInformation
End Sub

That worked! Thank you so much!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.