Solved

I would like to delete all images in an excel  worksheet

Posted on 2011-03-08
11
240 Views
Last Modified: 2012-05-11
I have images in a worksheet that I would like to delete in one shot.  The images are in cells
A6 to L1000.
0
Comment
Question by:dmalovich
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 35070110
- Select cells Ag:L1000
- Press Ctrl + G and click Special
- Select Objects then click OK
- Press delete on your keyboard
0
 
LVL 6

Expert Comment

by:akajohn
ID: 35070129
Backup all files as usual!
0
 
LVL 1

Expert Comment

by:TonyWong
ID: 35070141
Simplest way is to drag selection box over the images and then hit the 'Delete' button.
I would advise to save the workbook before doing this though, just in case.
0
 

Author Comment

by:dmalovich
ID: 35070240
How can this be done in a macro using vba?
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35070263
Try this:

Sub DeleteObjects()
    ActiveSheet.DrawingObjects.Delete
End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:dmalovich
ID: 35070462
That deleted all objects.  I just wanted objects deleted in cells A6 to L1000
0
 
LVL 1

Expert Comment

by:TonyWong
ID: 35070590
That deleted all objects.  I just wanted objects deleted...

Please clarify as this quote is contradictory....
0
 

Author Comment

by:dmalovich
ID: 35070613
the vba macro deleted all objects on the sheet, even if they were in cell a1.  I would like to delete
all objects that are in cells A6 to L1000 range.
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 35070632
Have a look here:

http://p2p.wrox.com/pro-vb-6/47491-clearing-drawing-objects-given-excel-range.html
Sub testit()
WipeOffRng Selection
End Sub

Sub WipeOffRng(WipeRange As Range)
Dim isect As Range
Dim wkSheet As Worksheet
Dim Shp As Shape
Dim rngShp As Range

    ' Set the worksheet
    Set wkSheet = WipeRange.Parent

    ' Loop through every shape
    For Each Shp In wkSheet.Shapes

        ' Dtermine the block range
        Set rngShp = Range(Shp.TopLeftCell, Shp.BottomRightCell)

        ' Test for any sort of overlap
        'If Not Intersect(WipeRange, rngShp) Is Nothing Then
        ' Test for fully inside

        Set isect = Intersect(WipeRange, rngShp)
        If isect Is Nothing Then

            Else
            Shp.Delete

        End If

    Next Shp

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 35070657
If you don't want to select the range before hand, then modify this:
Sub testit()
WipeOffRng Selection
End Sub

to this:

Sub testit()
WipeOffRng Range("A6:L1000")
End Sub
0
 

Author Closing Comment

by:dmalovich
ID: 35070755
Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql server Transfering records getting error #NAME? 6 40
Problem to With line 4 42
Excel 2016 Not Responding Issues 6 28
File not loading into PowerPivot 4 9
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now