Скачать или смотреть How to Delete Pictures in a Defined Range using VBA in Excel

Discover how to effectively delete specific pictures within a defined range in Excel using `VBA`. Follow these simple steps to streamline your workflow and solve the problem quickly.
---
This video is based on the question https://stackoverflow.com/q/74487790/ asked by the user 'Vecernice' ( https://stackoverflow.com/u/17821643/ ) and on the answer https://stackoverflow.com/a/74488827/ provided by the user 'Darren Bartrup-Cook' ( https://stackoverflow.com/u/4677305/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Problem with deleting pictures in range with VBA

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deleting Pictures in a Defined Range with VBA in Excel

If you've ever tried deleting pictures in a specific range within Excel using VBA, you might have encountered some frustrating hurdles. This can especially be the case when you have different shapes and pictures within your worksheet. Today, we’ll discuss a common problem and provide an effective solution for eliminating unwanted pictures from designated areas of your spreadsheet.

Understanding the Problem

You may have attempted to use some VBA code to delete pictures that fall within a specific range, such as B2:B7. Users often face issues like:

The code not executing properly, leading to unexpected stops or errors.

Being unable to differentiate between various objects, such as checkboxes versus pictures.

Confusion around properly identifying the correct objects to delete.

Here's a simplified version of the problematic code you might have initially used:

[[See Video to Reveal this Text or Code Snippet]]

Realizing the Limitation

When running the above code, you might have noticed that it can stop unexpectedly. The reason for this can be due to how Excel recognizes shapes and pictures, especially if there are other non-picture shapes involved.

The Solution: Using Shape Objects

To resolve the problem, it’s more effective to work with shape objects in your code instead of using the ActiveSheet.Pictures. The following code snippet does just that, allowing you to efficiently delete pictures within the specified range.

Step-by-Step Explanation of the Code

Here’s the corrected VBA code:

[[See Video to Reveal this Text or Code Snippet]]

Key Components of the Solution

Define the Shape Object:
The variable OBR is defined as a Shape, allowing the code to differentiate between different types of objects within the worksheet.

Iterate Through Shapes:
The For Each OBR In ThisWorkbook.Worksheets("Sheet1").Shapes line allows the loop to go through each shape in the specified worksheet.

Picture Type Check:
The conditional statement If OBR.Type = msoPicture Then ensures that only pictures are targeted for deletion, ignoring other shapes like text boxes or checkboxes.

Intersect Check:
The Intersect function checks if the TopLeftCell of the picture intersects with the defined range. If so, the picture is deleted.

Implementation Instructions

Open Excel: Open your Excel file where you wish to remove pictures.

Access the VBA Editor: Press ALT + F11 to open the Visual Basic for Applications editor.

Insert a New Module: Right-click on any of the items in the left pane, select Insert, and choose Module.

Paste the Code: Copy and paste the provided code into the new module.

Run the Code: Close the editor and return to Excel. Press ALT + F8, select Test, and click Run.

Conclusion

By using the Shape object instead of Picture, you can effectively target and delete images within a specific range in your Excel worksheet. This small change can prevent errors and ensure that your automation tasks run smoothly, saving you time and frustration.

Feel free to try out the code and adjust the range or sheet name as necessary for your specific needs. Happy coding!