---

VBA Count of Each Item in a List

Excel VBA: Count of Each Item in a List Using VBA and a Pivot Table

Got any Excel/VBA Questions? Free Excel Help.

With the aid of a Pivot Table and Excel VBA we can get a count of each item that appears in a list. Let's say the list currently resides in A1:A100, with A1 being a heading and many items appearing more than once. Copy the code below into any standard Module of the same Workbook that houses the list. If unsure how to do this, read instructions after the code below;

Sub CreateCountOf()

Dim strHead As String

Dim strSheetName As String

Dim strListAddress As String



    strHead = Selection.Cells(1, 1)

    strSheetName = "'" & ActiveSheet.Name & "'!"

    strListAddress = Selection.Address(ReferenceStyle:=xlR1C1)



        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

                strSheetName & strListAddress).CreatePivotTable TableDestination:="", _

                TableName:="CountOf"

        

   

        ActiveSheet.PivotTables("CountOf").AddFields RowFields:=strHead

        With ActiveSheet.PivotTables("CountOf").PivotFields(strHead)

            .Orientation = xlDataField

            .Caption = "Count of" & strHead

            .Function = xlCount

        End With

    

        ActiveWorkbook.ShowPivotTableFieldList = False

        Application.CommandBars("PivotTable").Visible = False

    

End Sub

To add the code, open the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) then go to Insert>Module and paste in the code above. To run the macro, go to Tools>Macro>Macros (Alt+F8) select the macro name and click "Run".

 

See also:

Count Or Sum Specified Number In a Single Cell

Count Distinct Values Once/Count Repeated Entries Only One Time

Custom Excel Function (UDF) that will Count Words in Excel

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•