In this post we will see how could we count number of images/shapes/objects in a sheet in excel.
In any standard excel dashboard, we find several objects like graphs, some images or any objects like arrow bar or a rectangular object filled with some information etc.
Below is a simple example of an excel sheet with some images present in it and we will try to count the number of images in this sheet in excel.
As you could see in the pic above we have some images/objects/shapes available in a sheet in excel and we will use a small VBA code to count the number of objects available here.
Step 1
Press Alt+F11 to open the VB editor in excel as shown below or alternatively you could click “Visual Basic” icon under Developer tab in excel ribbon as shown below.
Step 2
You will get the editor screen as shown below and now insert a module here from Insert Menu of the editor.
Step 3
Now paste this small VB code in the module which we have just created.
Sub Count_Shapes()
Dim counter As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
counter = ActiveSheet.Shapes.count
Range(“E2”).Value = counter
Next
End Sub
The counter variable that I have created here will hold the count of images/objects/shapes in a sheet in excel and I am writing the value of counter variable in our excel sheet in cell E2.
Step 4
Now run the macro by pressing F5 to count number of shapes/images/objects in any excel sheet.
Now go back to cell E2 of the sheet where oi have written the count of images and you will find the value 3 has been written there as we have 3 objects available in our excel sheet here as shown below.
You could either write this counter variable which has hold the count of number of images in any excel cell or could use it further in the program based on your requirement.
This program works for any type of excel objects/images and shapes you would like to count in excel.
Hope this helped.
