Deleting images in sheet

  • Hey guys,


    I need to delete a section of a worksheet, for instance C11:Q133.


    Now in between there, I have some images that I need to remove, how would I do this?
    Right now, I need to delete these cells, but when I do, it does not delete the images, just shrinks them so small you can't see them. Is there an easy code to select all logos within a range and delete?

  • Re: Deleting images in sheet


    Hi,


    Try this, it should delete shapes that are within a given range[vba]Sub Demo()


    DeleteShapesInRange Sheet1.Range("C11:Q133")

    End Sub
    Sub DeleteShapesInRange(rng As Range)


    Dim shpTemp As Shape
    Dim lngIndex As Long

    With rng.Parent
    For lngIndex = .Shapes.Count To 1 Step -1
    If Not Intersect(rng, .Shapes(lngIndex).TopLeftCell) Is Nothing Then
    If Not Intersect(rng, .Shapes(lngIndex).BottomRightCell) Is Nothing Then
    .Shapes(lngIndex).Delete
    End If
    End If
    Next
    End With

    End Sub[/vba]

    [h4]Cheers
    Andy
    [/h4]

  • Re: Deleting images in sheet


    Hey Andy, ran into error.


    The first sheet I did this on it worked great, but when I copied the module and stuff to another sheet, I get an error on this line:


    Code
    If Not Intersect(rng, .Shapes(lngIndex).TopLeftCell) Is Nothing Then


    Any ideas why? The wierd thing is it is written exactly as the other sheet, but still giving an error everytime at same point.

  • Re: Deleting images in sheet


    I wish I could, but 1)its too big, and it would be difficult to cut it down, because I have it set up to link to another Workbook, which is why I need to delete the images.


    The wierd thing is, the first workbook I did it on worked perfectly, pasted to another (separate workbook) and it gives the error at the same place everytime. Now the only thing I changed was:


    Code
    DeleteShapesInRange ActiveSheet.Range("C11:Q600")


    Now in your code, you have

    Code
    Dim shpTemp As Shape


    but it is not referred to in the rest of the code. does it not need to?

  • Re: Deleting images in sheet


    No shpTemp was a variable I intended to use to loop through the shapes before realizing I was deleting them and the For Each gets confused if you delete stuff within the loop.


    What is the actual error?

    [h4]Cheers
    Andy
    [/h4]

  • Re: Deleting images in sheet


    I seem to have gotten it to work. I added A for loop using your ShpTemp declaration:



    Seems to work on both Workbooks now. Since I have a varied number of images from one workbook to the next, I figure I would try adding that loop. Seems to have worked.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!