Forum Discussion
How do Count how many shapes are on a sheet?
Shapes can be charts, callouts, diagrams many types of shapes exists.
if you want to count particular one of them then you can use the code below and you can change the shp.type on any of the below listed ShapeType Enumeration to count particular ones or exclude or include shape types. if you remove the shape type then it will count anything that is classified as shapes in Excel
Sub Test2() Dim shp As Shape For Each shp In Sheet1.Shapes If shp.Type = 1 Then Counter = shp.ID + 1 End If Next MsgBox Counter - 2 End Sub
Thank you very much for your answers.
In my case I am using 10 different shapes in a sheet that are repeated several times each one.
I would like to count at the end of the sheet how many of them I have you all over sheet.
However, please note that some are lines of different desing, different circles or rectangles or basic forms.
Looking forward to hearing from you. best regards, Juan
- JamilNov 19, 2018Bronze Contributor
You can use the code below and it will add new sheet and will list all of the shapes with their name and location, then you can pivot it to see how many of which you have.
Sub ListShapes() Dim Wks As Worksheet Dim shp As Shape Dim nRow As Long Sheets.Add Cells.Clear Cells(1, 1) = "Worksheet" Cells(1, 2) = "Shape" Cells(1, 3) = "Type" Cells(1, 4) = "OnAction" Cells(1, 5) = "Hyperlink" Cells(1, 6) = "TopLeft" Cells(1, 7) = "BotRight" Cells(1, 8) = "Height" Cells(1, 9) = "Width" Cells(1, 10) = "Autoshape" Cells(1, 11) = "Form" nRow = 1 On Error Resume Next For Each Wks In ActiveWorkbook.Worksheets For Each shp In Wks.Shapes nRow = nRow + 1 Cells(nRow, 1) = "'" & Wks.Name Cells(nRow, 2) = shp.Name Cells(nRow, 3) = shp.Type Cells(nRow, 4) = shp.OnAction Cells(nRow, 5) = shp.Hyperlink.Address Cells(nRow, 6) = shp.TopLeftCell.Address(0, 0) Cells(nRow, 7) = shp.BottomRightCell.Address(0, 0) Cells(nRow, 8) = shp.Height Cells(nRow, 9) = shp.Width Cells(nRow, 10) = shp.AutoShapeType Next shp Next Wks done: nRow = nRow + 0 End Sub
- juan jimenezNov 20, 2018Iron Contributor
Hi Jamil,
your code was very useful. thank you very much.
could i ask for some more help regarding the project I am working with? can i send you the excel so that you can see the purpose of the code?
- JamilNov 20, 2018Bronze Contributorjuan,
you are welcome. you can post your excel here if you want to.