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".
|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.