VBA Macro to find distinct items in an Excel spreadsheet column and count number of times found

Just in case of use to anyone, a quick macro that loops through a column on an excel spreadsheet looking at each different value found, and getting the count of the number of times found…


Sub countValuesInColumn(column As Integer, startRow As Integer)

'Get the frequency of distinct items found in a column
'Assumes fewer than 100 different distinct values
'@author: David Boyce 
'@created: 2015-09-24

Dim activeRow, foundTotal As Variant
Dim activeValue, itemsArray(100, 1), msgTxt As String

activeRow = startRow

Do While NotFound = False
  If ActiveWorkbook.ActiveSheet.Cells(activeRow, column) = "" Then Exit Do
  activeValue = ActiveWorkbook.ActiveSheet.Cells(activeRow, column)
  
    For n = LBound(itemsArray) To UBound(itemsArray)
    
      If itemsArray(n, 0) = "" Then
        itemsArray(n, 0) = activeValue
        itemsArray(n, 1) = 1
        Exit For
      Else
        If itemsArray(n, 0) = activeValue Then
          itemsArray(n, 1) = itemsArray(n, 1) + 1
          Exit For
        End If
      End If
      
    Next
  
    activeRow = activeRow + 1
    
Loop

For n = LBound(itemsArray) To UBound(itemsArray)
  If itemsArray(n, 0) = "" Then Exit For
  msgTxt = msgTxt & itemsArray(n, 0) & " : " & itemsArray(n, 1) & vbCrLf
  foundTotal = foundTotal + itemsArray(n, 1)
Next

msgTxt = msgTxt & foundTotal
MsgBox msgTxt

End Sub