HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Clearing Pictures Code

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
clearingcodepictures

Problem

The following code removes all pictures that are placed into Column A. When running the code however I have noticed it runs fairly slow. This will be attached to a command button that will be located in A1, it is important that the deleting of pictures doesnt target this command button. Any suggestions?

' Clears All Pictures
    Sub DeleteAllPics()
    Columns("A:A").Replace What:="No Picture Found", Replacement:="", LookAt:=xlPart
    Dim pic As Object
    For Each pic In ActiveSheet.Pictures
    pic.Delete
    Next pic
    End Sub

Solution

I forced @proxy156 to start a new question which we discussed here
Finding and pasting images into a specific cell

I just can't figure out how the formatting works in comments here.
The problem here is that it takes a minute to delete about ~3000 pictures
Also there was a problem with different shape objects that should not be deleted (Buttons)

This code should work a bit better

Sub DeleteAllPics()

    Dim wks         As Worksheet
    Dim shp         As Shape
    Dim picArray()  As String
    Dim index       As Integer

    On Error GoTo ErrorHandler

    Set wks = ActiveSheet
    index = 1
    For Each shp In wks.Shapes
        If shp.Type <> msoFormControl Then
            ReDim Preserve picArray(1 To index)
            picArray(index) = shp.Name
            index = index + 1
        End If
    Next shp

    wks.Shapes.Range(picArray).Delete

ExitRoutine:
    Set wks = Nothing
    Set shp = Nothing
    Erase picArray
    Exit Sub

ErrorHandler:
    MsgBox Prompt:="Unable to find photo", _
           Title:="An error occured", _
           Buttons:=vbExclamation
    Resume ExitRoutine

End Sub

Code Snippets

Sub DeleteAllPics()

    Dim wks         As Worksheet
    Dim shp         As Shape
    Dim picArray()  As String
    Dim index       As Integer

    On Error GoTo ErrorHandler

    Set wks = ActiveSheet
    index = 1
    For Each shp In wks.Shapes
        If shp.Type <> msoFormControl Then
            ReDim Preserve picArray(1 To index)
            picArray(index) = shp.Name
            index = index + 1
        End If
    Next shp

    wks.Shapes.Range(picArray).Delete


ExitRoutine:
    Set wks = Nothing
    Set shp = Nothing
    Erase picArray
    Exit Sub

ErrorHandler:
    MsgBox Prompt:="Unable to find photo", _
           Title:="An error occured", _
           Buttons:=vbExclamation
    Resume ExitRoutine

End Sub

Context

StackExchange Code Review Q#92687, answer score: 4

Revisions (0)

No revisions yet.