patternMinor
Clearing Pictures Code
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 SubSolution
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
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 SubCode 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 SubContext
StackExchange Code Review Q#92687, answer score: 4
Revisions (0)
No revisions yet.