(VBA) (Excel) Get and count unique values in a column-CodePudding

Hello everyone and have a good day! =)

I have a table with a column (say A:A) i want to get unique values from, count them and write them down, i have:

Russian Federation
Italy
USA
Estonia
Belarus
Netherlands
Russian Federation
Russian Federation
Russian Federation
USA
USA
Russian Federation
Russian Federation

Is there some way in VBA to write for example in neighbouring column (say cell B1 or column B:B, how's easier) this:

Russian Federation 6
Italy 1
USA 3
Estonia 1
Belarus 1
Netherlands 1

Thank you much in advance!

CodePudding user response：

Please, try the next code. It will return in columns "B:C":

Sub uniqueValuesAA()
   Dim sh As Worksheet, lastR As Long, arr, arrFin, i As Long, dict As Object 
   
   Set sh = ActiveSheet 'use here the sheet you need
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
   arr = Range("A1:A" & lastR).Value
   Set dict = CreateObject("Scripting.Dictionary")
   For i = 1 To lastR
        dict(arr(i, 1)) = dict(arr(i, 1))   1
   Next i
   arrFin = Application.Transpose(Array(dict.Keys, dict.items))
   sh.Range("B1").Resize(dict.count, 2).Value = arrFin
End Sub