Count Number Of Images/Shapes/Objects In A Sheet In Excel

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.




sheet containing images/objects/shapes 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.

vba code to count number of images/shapes/objects in a sheet in excel

Step 2

You will get the editor screen as shown below and now insert a module here from Insert Menu of the editor.




writing vba code to count number of images/shapes/objects in a sheet in excel

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.

vba code showing how to count number of images/shapes/objects in a sheet in excel

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.

varibale showing number of images/objects/shapes available in the sheet in excel




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.

 

Share The Knowledge

Random Posts

Leave a Reply