VBA Count of Each Item in a List

< Back to Search results

 Category: [Excel]  Demo Available 

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:="", _




        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.


stars (0 Reviews)