Forum Discussion
How do Count how many shapes are on a sheet?
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
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.- juan jimenezNov 21, 2018Iron Contributor
Hello again on thank you very much for your help.
As you come see we are trying to do some architectural work in an Excel sheet. We were supposed to be using AutoCAD but as we are full supporters of Excel we are trying to do it for the first time in this worksheet.
We are trying to measure in each floor how many items are we going to use so that it could be linked to another sheet were prices per item with give us the amount of material used.
When we tried to count the shapes it is a bit difficult for us to distinguish how many items per flat we do need. If you look into columns K&L you can have an idea of what we are trying to measure exactly. I wonder if you could help us to obtain in column M the number of items per floor.
All the best!