Solved

Macro that will delete only the shapes in a specified range

Posted on 2011-09-23
5
390 Views
Last Modified: 2012-05-12
For some reason, perhaps because my network is so slow, I find that the standard macro for this takes up a lot of time. (See code below) Just wondering if there is anything new in this area. I just can't seem to give up on the idea that there is a VBA code that selects/deletes all the shapes in Range("B2:R100") and only those shapes :-)

Thanks,
John
Sub deleteShapes()
Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
            If Not Intersect(shp.TopLeftCell, Range("B2:R100")) Is Nothing Then
                    shp.Delete
            End If
    Next
End Sub

Open in new window

0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
kgerb earned 250 total points
ID: 36590933
Hello John,
This is worth a shot.  It may be that the actual deletion of the object is taking the time.  Try adding the names of the shapes to an array and then deleting them all at once.  Not for certain it will help but it might.

Kyle
Sub deleteShapes()
Dim shp As Shape, Count As Long
Dim arr() As String
Count = 0
For Each shp In ActiveSheet.Shapes
    If Not Intersect(shp.TopLeftCell, Range("B2:R100")) Is Nothing Then
        ReDim Preserve arr(Count + 1)
        arr(Count) = shp.Name
        Count = Count + 1
    End If
Next
ActiveSheet.Shapes.Range(arr).Delete
End Sub

Open in new window

0
 
LVL 7

Assisted Solution

by:hippohood
hippohood earned 250 total points
ID: 36591349
it will work quicker if instead of deleting tem one by one you do the following:
- move the shapes to an unsued range (say, column AZ), instead of deleting them
- then delete the column AZ altogehter

Something like code below
Sub deleteShapes()
dim theLeft as long
theLeft = Columns("AZ:AZ").Left
Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
            If Not Intersect(shp.TopLeftCell, Range("B2:R100")) Is Nothing Then
                    shp.Left=theLeft
            End If
    Next
    
Columns("AZ:AZ").Delete Shift:=xlToLeft

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36713716
Hi kqerb, I got an error on the last line but thanks for posting. Hippohood, I can see some definite = value in your code but in my experience when I delete columns or rows, the shapes in them don'e get deleted.  

I guess there's still no way to directly select only the shapes in a specified arrange. Oh well, thanks for your suggestions. :-)

Thanks,
John
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36713773
Thanks again.

- John
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36716750
John, after 2000 questions you should know the drill.  Please give us the opportunity to "earn" an A grade before closing the question with a lower grade.

What exactly was wrong with the code?  You got an error on line 12?  Was the sheet which you are deleting all the shapes on active when you ran the code?

Try the following, I changed Activesheet to a specific sheet.  Replace "Sheet1" with the name of your sheet.

Kyle
Sub deleteShapes()
Dim shp As Shape, Count As Long
Dim arr() As String
Dim sSheet As String

Count = 0
sSheet = "Sheet1"

For Each shp In Sheets(sSheet).Shapes
    If Not Intersect(shp.TopLeftCell, Range("B2:R100")) Is Nothing Then
        ReDim Preserve arr(Count + 1)
        arr(Count) = shp.Name
        Count = Count + 1
    End If
Next
Sheets(sSheet).Shapes.Range(arr).Delete
End Sub

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

622 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