How to Delete All Pictures from Excel Worksheets Using VBA
Question details
The user needs to remove all picture objects from every worksheet in an Excel workbook simultaneously using a VBA macro.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up an Excel workbook by bulk removing images without accidentally deleting other objects like charts or grouped shapes.
- Observed behavior
- The existing VBA code deletes grouped shapes instead of specifically targeting and removing picture objects.
Before modifying and running the macro, ensure you have saved a backup copy of your Excel workbook, as VBA macro actions clear the undo history and cannot be reversed.
Update VBA Code to Target the msoPicture Shape Type
Modify your existing VBA macro to properly identify and delete only picture objects by checking for the correct shape type condition.
In Excel VBA, pictures, buttons, and grouped shapes all belong to the generic 'Shapes' collection. To delete only pictures, your loop must check the 'Type' property of each shape and only execute the delete command if the type corresponds to a picture.
With your Excel workbook open, press the 'Alt + F11' shortcut on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, double-click the specific Module that contains your current shape-deletion code.
Find the conditional IF statement inside your shape-looping code. Change the condition from checking for grouped shapes to targeting pictures by using: 'If shp.Type = msoPicture Then'.
Save your code, then press 'F5' or click the green 'Run' button on the toolbar to execute the updated macro. It will now loop through all worksheets and delete only the pictures.
Manage Worksheets and Macros Seamlessly with WPS Office
WPS Office Spreadsheet provides excellent compatibility with Microsoft Excel macros, allowing you to seamlessly run, edit, and execute VBA code to automate bulk tasks like deleting pictures.
- 1. Download and install WPS Office: Download WPS Office from the official website and complete the quick installation process.
- 2. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm file containing the picture-deletion macro.
- 3. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or 'Visual Basic'.
- 4. Run your macro: Execute your updated VBA code to instantly clear all pictures from your worksheets, exactly as you would in Microsoft Excel.
Frequently Asked Questions
Why does my VBA code delete buttons and text boxes instead of pictures?
In VBA, pictures, buttons, and text boxes are all classified under the 'Shapes' collection. If your loop deletes every item in this collection without a conditional check (like 'shp.Type = msoPicture'), it will remove all shape objects indiscriminately.
How do I loop through all worksheets in a workbook using VBA?
You can use a 'For Each' loop to iterate through the 'Worksheets' collection. A standard approach is declaring a worksheet variable (e.g., 'Dim ws As Worksheet') and then using 'For Each ws In ActiveWorkbook.Worksheets' to execute your code on every sheet.
Can I undo a macro after it accidentally deletes the wrong shapes?
No, actions performed by a VBA macro generally clear Excel's undo stack, meaning you cannot use 'Ctrl + Z' to revert the changes. This is why it is highly recommended to save a backup of your file before testing or running new macros.
