VBA Count Distinct Special Cells in a Range

[Stephen_IV]

I have a list of over 75,000 names. I am trying to get a unique count of any cell that has special characters in then except the comma (,). For the data below the count could be 7. Thanks in advance!

Billard, Ny'aniah S.D.
Kolman, Ja'Niyla A'Marie
Johnson, Dave
Holmes, Lord-Ken'th
Hook-Boullai, Jae'Zon Delomte
Jean-Franncois, Christ-Anne
Jones, Mike
Peterson, Ja'Nyhia Jer'Mirna
Wilson, Sanii'yah-lina Storla
Jones, AL

 

[Peter_SSs]

Edit: After posting the function below, I am wondering about "unique count". Not sure what that might mean in this context as your sample didn't include any duplicate cell values.

Try this user-defined function. It should count a cell if it contains anything other than the 26 letters, space or comma.

Function CountSpecial(r As Range) As Long
  Dim a As Variant, itm As Variant
  
  a = r.Value
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^A-Za-z ,]"
    For Each itm In a
      If .Test(itm) Then CountSpecial = CountSpecial + 1
    Next itm
  End With
End Function

Excel Workbook
	A	B
1	Billard, Ny'aniah S.D.	7
2	Kolman, Ja'Niyla A'Marie
3	Johnson, Dave
4	Holmes, Lord-Ken'th
5	Hook-Boullai, Jae'Zon Delomte
6	Jean-Franncois, Christ-Anne
7	Jones, Mike
8	Peterson, Ja'Nyhia Jer'Mirna
9	Wilson, Sanii'yah-lina Storla
10	Jones, AL
Sheet1

 

[Stephen_IV]

Perfect! Thanks Peter_SSs for your help and all the help you give to this site! I truly appreciate it!

 

[Peter_SSs]

Perfect! Thanks Peter_SSs for your help and all the help you give to this site! I truly appreciate it!

You're welcome. Glad it worked for you.