Excel: Loop through Objects within a given range 4

[kjv1611]

I'm using Excel 2007 currently, but for the purposes of this example, but I doubt I matters for something of this sort.

I found how to loop through all objects within the pictures collection for a specific worksheet, and I've done that, deleting all in a worksheet - just a copy/paste operation that was driving me bonkers.

However, my code (posted below) seems to run extremely slow - specifically, it runs slowest until it finds the first object. That made me wonder, is there a better way to do this, and specifically, can I narrow the scope to a set range somehow? I tried setting the Range, and looking at a Pictures collection for a range, but apparently one doesn't exist.

Here's my working code for looping through all pictures in a Worksheet:

Private Sub RemovePictures()
[green]'Code programmed for removing all the pictures that come along with a specific copy/paste operation[/green]

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim p As Object [green]'pictures[/green]
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    For Each p In ws.Pictures
        p.Select
        [green]'MsgBox p.Name[/green]
        p.Delete
    Next p
    
    Range("A1").Select
    
    Set p = Nothing
    Set ws = Nothing
    Set wb = Nothing
    
    MsgBox "Operation Complete! " & Chr(13) & "All pictures deleted!", vbInformation, "Finished!"
    
End Sub

Then I tried adapting the same code a couple of ways (one with a Range variable, the other just by referencing the a range within the worksheet. The latter is what is now showing in the code (non-working as of now)):

[green]'Try with a range:[/green]
Private Sub RemovePictures1()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim p As Object 'pictures

    Application.ScreenUpdating = True

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet

    For Each p In ws.Range("A1:B240").Pictures
        p.Select
        [green]'MsgBox p.Name[/green]
        p.Delete
    Next p

    Range("A1").Select

    Set p = Nothing
    Set ws = Nothing
    Set wb = Nothing

    MsgBox "Operation Complete! " & Chr(13) & "All pictures deleted!", vbInformation, "Finished!"

End Sub

This code is honestly not very important at this time, but I wanted to use it as a learning experience. Can anybody offer any advice/suggestions/references/examples for better handling this particular operation? Surely there's a way for this to run faster.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

You do have some unnecessary code, for instance you should not & need not select each object...

Private Sub RemovePictures()
'Code programmed for removing all the pictures that come along with a specific copy/paste operation

    Dim p As Object 'pictures
    Dim iCnt As Integer
    
    For Each p In ActiveSheet.Pictures
        p.Delete
        iCnt = iCnt + 1
    Next p
    
    ActiveSheet.Range("A1").Select
    
    MsgBox "Operation Complete! " & Chr(13) & iCnt & " pictures deleted!", vbInformation, "Finished!"
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Don't Select each picture; you don't need to and it slows things down (and then you can also get rid of the final Range("A1").Select since the original selection won't have moved ...)

Also, I'd be tempted to declare

Dim p As Picture

rather than

Dim p As Object

as it is fractionally faster (although not significantly so on modern hardware)

 

[kjv1611]

Thanks all, I'll redo the copy/paste operation and give it a whirl. I wasn't sure whether you could just declare a Picture variable, that's why I just stuck with generic Object. Makes sense, though, if there is a pictures collection.

As for the selection thing, I was doing that initially to see if I was getting it right, and stepping through the code. I suppose I should've taken that out. p

I'll post back ASAP.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks for your suggestions.

I removed all the object variables except for the one picture variable, and took out all the selections, and added the counter. The counter was a nice addition that I didn't think about b/c I was able to see just how many pictures I removed.

In the specific instance, I removed 956 pictures (little bitty pictures - was on a website where I was viewing personal info, and I wanted to pull it into Excel for calculations.)

It still did take a little while, but I think it ran quicker than previoulsy.

Here is the current code:

Private Sub RemovePictures()
    Dim p As Picture 'pictures
    Dim iCnt As Integer

    For Each p In ActiveSheet.Pictures
        p.Delete
        iCnt = iCnt + 1
    Next p
    
    Set p = Nothing
    
    MsgBox "Operation Complete! " & Chr(13) & iCnt & " pictures deleted!", vbInformation, "Finished!"
    
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[DaveInIowa]

I'm not able to find a Picture or Pictures class in Intellisense or the Object Browser. Could someone shed some light on why that is? I thought I could rely on these to give me a picture (no pun intended) of the application's object model but now I'm wondering what other items I've been overlooking all this time.

 

[SkipVought]

Dave, I have the same question. I searched for a Pictures Collection of the Worksheet Object and NADA.

BUT...

it complied! ???

Go figger.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

..furthermore, if you do a watch on Activesheet.Pictures, it defines the object as Type Variant/Object/Pictures and opens all the properties thereof.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

The Pictures Object isn't the first I've come across where that occurs.

Skip, how do you put a watch on a variable? Seems like I've read that before, but haven't the foggiest at the moment..

What would it have to do with the Variant/Object/Pictures deal? Any clues from anybody on that?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

faq707-4594

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

how do you put a watch on a variable
Have a look here:
faq707-4594

As for the code, what about this ?

Private Sub RemovePictures()
Dim iCnt As Integer, i As Integer
With ActiveSheet.Pictures
  iCnt = .Count
  For i = iCnt To 1 Step -1
    .Item(i).Delete
  Next
End With
MsgBox "Operation Complete! " & Chr(13) & iCnt & " pictures deleted!", vbInformation, "Finished!"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveInIowa]

kjv1611 - Do you recall how you came to use the .Pictures collection to begin with?

Since .Pictures was a worksheet collection, I thought there might be a .Clear, .RemoveAll or .ApplyPaintThinner method to process all pictures without looping, but no luck. To keep beating a dead horse, here's a couple more possibilities:

Dim s As Shape
For Each s In ws.Shapes
    If (s.Type = msoPicture) Then s.Delete
Next
    
' Or (if you know they're all pictures)
    
ws.Shapes.SelectAll
Selection.Delete

 

[strongm]

Both Picture and Pictures are hidden members. When using the Object Browser right-click it and select "Show Hidden Members" from the menu that appears. Why are they hidden? Presumably because Microsoft are trying to encourage use of the Shapes collection, of which a picture is but one of the many types a Shape can be ...

>Since .Pictures was a worksheet collection, I thought there might be a .Clear

Ooh - hang on ... good point. So we can do:

[blue]Private Sub RemovePictures(Optional ws As Worksheet)
    Dim iCnt As Long
    
    If ws Is Nothing Then Set ws = ActiveSheet
    
    iCnt = ws.Pictures.Count
    ws.Pictures.Delete
    
    MsgBox "Operation Complete! " & Chr(13) & iCnt & " pictures deleted from " & ws.Name, vbInformation, "Finished!"

End Sub[/blue]

 

[DaveInIowa]

That's great information strongm. Thanks so much! => *

 

[kjv1611]

kjv1611 - Do you recall how you came to use the .Pictures collection to begin with?

Just Googled around, hoping for a way to loop through and delete pictures.... and viola! ;0)

Of course, some o' the folks 'round here didn't need the viola part!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

strongm, I'll have to give your code a try and see if it indeeed works. If it does, then it should work much faster... I'll give it a go, and post back.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Ok, WOW! That was a HUGE difference in performance! Talking almost instantly FINISHED!

Way to go, strongm!

Also, thanks to Skip and PHV for the reference for putting a watch on a variable.

--

"If to err is human, then I must be some kind of human!" -Me