Where Are the Hidden AutoShapes??

[Cyberdude]

I have had this problem a number of times over the years, and I still don?t understand it.

Yesterday I did a count of all the AutoShapes on different sheets of a workbook using ActiveSheet.Shapes.Count, and it says that I have 2020 on one sheet and 1630 on another. Then I used ActiveSheet.DrawingObjects.Select to highlight all the ?objects? on each sheet. Visually there were not anywhere near as many ?objects? as the AutoShape count would indicate.

Let me digress to say that I accidentally found over a thousand AutoShape block arrows (which I use a lot of on certain sheets) that were squashed down to nearly a one-line thickness and all on top of each other. I have a macro that will assist me in deleting such unwanted collections, so I was able to get rid of the group that I found. That?s when I decided to do a count. I have been having problems with ?Not enough resources to display?, and I am sure that the hidden AutoShapes are certainly a contributing factor to this nuisance.

My question is, how do I find the hidden AutoShapes? Is there, for example, a way to find an AutoShape if I know its number? The shapes I deleted yesterday had large numbers like 6302, which is pretty scary. Anyone else ever have this problem?

[Jacob Hilderbrand]

Maybe this will help:

[vba]

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes

MsgBox Shp.TopLeftCell
Shp.TopLeftCell.Select
Stop

next
[/vba]

[Cyberdude]

Hi, Jake! Thanx for the suggestion ... looks like a winner.
Sid

[Jacob Hilderbrand]

Glad to help.

Well at least that will tell you where it is at. You can also add lines to change the size so you can see it (if it is shrunk down).

[mdmackillop]

Hi Sid,
How about some code to delete all shapes entirely contained in a range of cells.

[Cyberdude]

OK, with help from you guys I have successfully written several macros to investigate the "hidden" AutoShapes.
I know where they are, what they are, and their numbers.
I also have a macro to delete large numbers of them that I have used successfully in the past.

Regretably, I found that I can neither see nor delete most of them. Some of them are identified as "Lines", whatever that means. All I see is blank space. Since this is not a new problem, I do know what to look for ... they just aren't there, and since I am unable to delete them by specifying their AutoShape numbers, I have to presume that the worksheets have been corrupted in some fashion. The few objects that I did delete reduced the workbook size significantly, so I know that's what causing the "out of resources" errors. Pffft!

Thank you all for your able assistance. It's been a good education!

[johnske]

Hi Sid,

Yes, I've also had this problem in the past and it's a real PITA. From memory, I think I got the problem by deleting rows that had some out-of-sight shapes such as text boxes somewhere on them (or some may've been squashed up to the thickness of a line). The rows are deleted but the shapes were not cut before deleting, so somewhere deep in the recesses of the machines memory it's obediantly saving up all these invisible and unobtainable shapes for me. So yes, in effect you could say the worsheet's been corrupted.

Now, just to be sure, I always make sure I cut all shapes and do a save before deleting any rows.

HTH,
John

[Cyberdude]

Thanks for the reply, John. I think what you described is what has happened. I tend to be negligent at times about deleting AutoShapes before deleting lines and sheets. I suspect that deleting sheets can also cause the problem. But I'm learning the hard way. I convinced more people have the same problem than they realize.

[johnske]

... I convinced more people have the same problem than they realize.

Actually, you're right Sid. This whole discussion brought to mind a part of a problem I had with someones workbook on another forum.

As it was, it showed the workbook was 5+MB and was as slow-as. Considering the amount of data in it, 5+MB was quite unbelievable. I ran Jakes clean-up macro which brought it back to 1.5MB - but that was still way too much.

So, following this discussion, I just now copied and special-pasted the row headers and their formats (and the single line of code) from what I suspected was the problem sheet to a new sheet (n.b. this was all that was on that sheet). Deleted the original sheet and saved - Voila! what was 1.5MB instantly became 83Kb and is now FAST!!

[Cyberdude]

John, if you think that a 5 Mb book is large, I was having trouble with one that was about 7.5 Mb for no discernable reason. I kept running out of resources to display. So I ran my diagnostic macro and found that there were 20,836 RightArrows lurking in there somewhere. That was too big a job to track down and delete those shapes, so I had to rebuild the sheet this weekend. The book is now about 3.5 MB. No more out of resources complaints.

[mdmackillop]

I had to split one of my workbooks when it reached 27.5MB with no shapes at all. The only problem was that macros ran exceedingly slowly when it was open. I've no problem now with the 16.5 & 10MB resultant books, although as my data accumulates weekly, I'll have to keep an eye on them!
Regards
MD

[johnske]

John, if you think that a 5 Mb book is large, I was having trouble with one that was about 7.5 Mb for no discernable reason. I kept running out of resources to display. So I ran my diagnostic macro and found that there were 20,836 RightArrows lurking in there somewhere. That was too big a job to track down and delete those shapes, so I had to rebuild the sheet this weekend. The book is now about 3.5 MB. No more out of resources complaints.

Sid,

It wasn't that I thought 5MB was large in itself, it was just large when you considered what this particular workbook actually contained - bugger all.

I've since found the OP was manually pasting thousands of lines into the book to do their calculations in it, and then (after printing {I assume}) clearing the contents for another paste etc. As we really don't know exactly what's being pasted every time - could be styles, formats, shapes etc. if they're doing a straight paste, the worksheet could quite easily become bloated. So for now I've just altered their code to delete (instead of clear) all pasted rows after they run their procedure. But I'd really like to get them to always do their copy and paste via a procedure so there's some control (by always using paste special) over what's being pasted

[malik641]

What about making the shapes bigger so you can find them easier???

[vba]Sub test()
Dim objShape As Shape

For Each objShape In ActiveSheet.Shapes
objShape.ScaleHeight 15, msoFalse
objShape.ScaleWidth 15, msoFalse
Next objShape

End Sub[/vba] Yes? No?