This is a horror story with a happy ending.
Imagine an excel file with over 180 worksheets in it. Now imagine that each worksheet contains an image or two to make it look nice and professional and all.
Now imagine that as the company evolved and the excel file grew from one single sheet to over 180, so did the brand of this company. As a result, different worksheets started to feature different brand images. Very often these logos had different dimensions.
To make things worse, imagine that some of the worksheets contain smaller image icons that are actually kind of helpful.
How can I get rid of all those ugly and outdated images in the entire workbook, but keep the smaller icons intact?
The solution is quite simple. We will go through each and every worksheet and remove each and every image that fits a specific size requirement. What will take us hours of manual labor a macro will do in an instant.
Here is a macro you can copy and paste into a VBA module (here’s how) of your excel file to get rid of all images in your entire excel file fitting a specific size criterium.
Sub AllPicts()
With Application
.ScreenUpdating = False
Dim pict As Shape
On Error Resume Next
For Each sh In Sheets
For Each pict In sh.Shapes
If pict.Type = msoPicture And pict.Width > 200 And pict.Height > 35 Then
sh.Activate
pict.Delete
n = n + 1
End If
Next pict
Next sh
MsgBox n & " pictures were deleted successfully."
.ScreenUpdating = True
End With
End Sub
As you can see the size of the pictures to be deleted in specified by this:
pict.Width > 200 And pict.Height > 35
In our example we will delete all images that are wider than 200 pixels and higher than 35 pixels. Feel free to modify these two parameters as you see fit.
This boilerplate can help you not only get rid of all images in all worksheets, but can be further modified to replace a variety of images with a specific image, or adjust the position of these images, or … the list goes on.
After going through this pain I have realized that having images and other branding elements in an excel file is a terrible, terrible idea. Some things are just not meant to be branded.