Deleting "hidden" charts on a worksheet

Hi everyone,

I've been playing around w/ a worksheet that contains data and charts,
inserting and removing multiple rows at a time.  Unfortunately, I did
not change the properties of the charts and they were all set to "Move
and Size w/ Cells".  When I deleted a series of rows, these charts
disappeared and left a line across the spreadsheet where they used to
be.  I've tried everything to get rid of them (because they are now
calculating incorrectly and giving me error messages), including
deleting the rows where they appear, trying to select them on the
sheet, but nothing works.  The best scenario would be to select and
delete them but obviously this isn't possible.  Any thoughts?

Thanks,
Louis

0
LJLevine (1)
11/27/2006 10:27:18 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
470 Views

Similar Articles

[PageSpeed] 51

Try this. Be advised it will delete all shapes on the active sheet. Is this 
what you want to do?
Sub deleteshapes()
For Each s In ActiveSheet.Shapes
s.Delete
Next s
End Sub


-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<LJLevine@gmail.com> wrote in message 
news:1164666438.091311.295270@l39g2000cwd.googlegroups.com...
> Hi everyone,
>
> I've been playing around w/ a worksheet that contains data and charts,
> inserting and removing multiple rows at a time.  Unfortunately, I did
> not change the properties of the charts and they were all set to "Move
> and Size w/ Cells".  When I deleted a series of rows, these charts
> disappeared and left a line across the spreadsheet where they used to
> be.  I've tried everything to get rid of them (because they are now
> calculating incorrectly and giving me error messages), including
> deleting the rows where they appear, trying to select them on the
> sheet, but nothing works.  The best scenario would be to select and
> delete them but obviously this isn't possible.  Any thoughts?
>
> Thanks,
> Louis
> 


0
dguillett1 (2487)
11/27/2006 10:39:22 PM
A little more discriminating:

Sub DeleteThinShapes()
  Dim s As Shape
  For Each s In ActiveSheet.Shapes
    If s.Height < 1 or s.Width < 1 Then
      s.Delete
    End If
  Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:uEmUhTnEHHA.3600@TK2MSFTNGP06.phx.gbl...
> Try this. Be advised it will delete all shapes on the active sheet. Is 
> this what you want to do?
> Sub deleteshapes()
> For Each s In ActiveSheet.Shapes
> s.Delete
> Next s
> End Sub
>
>
> -- 
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> <LJLevine@gmail.com> wrote in message 
> news:1164666438.091311.295270@l39g2000cwd.googlegroups.com...
>> Hi everyone,
>>
>> I've been playing around w/ a worksheet that contains data and charts,
>> inserting and removing multiple rows at a time.  Unfortunately, I did
>> not change the properties of the charts and they were all set to "Move
>> and Size w/ Cells".  When I deleted a series of rows, these charts
>> disappeared and left a line across the spreadsheet where they used to
>> be.  I've tried everything to get rid of them (because they are now
>> calculating incorrectly and giving me error messages), including
>> deleting the rows where they appear, trying to select them on the
>> sheet, but nothing works.  The best scenario would be to select and
>> delete them but obviously this isn't possible.  Any thoughts?
>>
>> Thanks,
>> Louis
>>
>
> 


0
jonxlmvpNO (4558)
11/28/2006 4:07:58 AM
Hello John. I read your note and the code worked perfectly (as designed) but 
I have a bunch of charts that are hidden (don't know why). Even when I use 
your discriminating code it gets all charts, buttons, everything. Is there a 
way to find them and delete them one at a time? Thanks in advance. Carl

"Jon Peltier" wrote:

> A little more discriminating:
> 
> Sub DeleteThinShapes()
>   Dim s As Shape
>   For Each s In ActiveSheet.Shapes
>     If s.Height < 1 or s.Width < 1 Then
>       s.Delete
>     End If
>   Next
> End Sub
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:uEmUhTnEHHA.3600@TK2MSFTNGP06.phx.gbl...
> > Try this. Be advised it will delete all shapes on the active sheet. Is 
> > this what you want to do?
> > Sub deleteshapes()
> > For Each s In ActiveSheet.Shapes
> > s.Delete
> > Next s
> > End Sub
> >
> >
> > -- 
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > <LJLevine@gmail.com> wrote in message 
> > news:1164666438.091311.295270@l39g2000cwd.googlegroups.com...
> >> Hi everyone,
> >>
> >> I've been playing around w/ a worksheet that contains data and charts,
> >> inserting and removing multiple rows at a time.  Unfortunately, I did
> >> not change the properties of the charts and they were all set to "Move
> >> and Size w/ Cells".  When I deleted a series of rows, these charts
> >> disappeared and left a line across the spreadsheet where they used to
> >> be.  I've tried everything to get rid of them (because they are now
> >> calculating incorrectly and giving me error messages), including
> >> deleting the rows where they appear, trying to select them on the
> >> sheet, but nothing works.  The best scenario would be to select and
> >> delete them but obviously this isn't possible.  Any thoughts?
> >>
> >> Thanks,
> >> Louis
> >>
> >
> > 
> 
> 
> 
0
Carl (99)
1/9/2007 8:41:00 AM
Hi,

Jon's code should only be deleting shapes that have a width or height of 
less than 1., so I'm not sure why it's deleting everything for you.

This small mod will require confirmation before deleting. It will also 
highlight the cells that the shape in covering.

Sub DeleteThinShapes()
   Dim s As Shape
   Dim strMsg As String
   Dim rngOrig As Range

   Set rngOrig = ActiveCell
   For Each s In ActiveSheet.Shapes
     If s.Height < 1 Or s.Width < 1 Then
       Application.Goto Range(s.TopLeftCell, s.BottomRightCell)
       strMsg = "Delete " & s.Name & _
" which is over cells " & Selection.Address
       If MsgBox(strMsg, vbYesNo) = vbYes Then s.Delete
     End If
   Next
   rngOrig.Select

End Sub

Cheers
Andy

Carl wrote:
> Hello John. I read your note and the code worked perfectly (as designed) but 
> I have a bunch of charts that are hidden (don't know why). Even when I use 
> your discriminating code it gets all charts, buttons, everything. Is there a 
> way to find them and delete them one at a time? Thanks in advance. Carl
> 
> "Jon Peltier" wrote:
> 
> 
>>A little more discriminating:
>>
>>Sub DeleteThinShapes()
>>  Dim s As Shape
>>  For Each s In ActiveSheet.Shapes
>>    If s.Height < 1 or s.Width < 1 Then
>>      s.Delete
>>    End If
>>  Next
>>End Sub
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>>news:uEmUhTnEHHA.3600@TK2MSFTNGP06.phx.gbl...
>>
>>>Try this. Be advised it will delete all shapes on the active sheet. Is 
>>>this what you want to do?
>>>Sub deleteshapes()
>>>For Each s In ActiveSheet.Shapes
>>>s.Delete
>>>Next s
>>>End Sub
>>>
>>>
>>>-- 
>>>Don Guillett
>>>SalesAid Software
>>>dguillett1@austin.rr.com
>>><LJLevine@gmail.com> wrote in message 
>>>news:1164666438.091311.295270@l39g2000cwd.googlegroups.com...
>>>
>>>>Hi everyone,
>>>>
>>>>I've been playing around w/ a worksheet that contains data and charts,
>>>>inserting and removing multiple rows at a time.  Unfortunately, I did
>>>>not change the properties of the charts and they were all set to "Move
>>>>and Size w/ Cells".  When I deleted a series of rows, these charts
>>>>disappeared and left a line across the spreadsheet where they used to
>>>>be.  I've tried everything to get rid of them (because they are now
>>>>calculating incorrectly and giving me error messages), including
>>>>deleting the rows where they appear, trying to select them on the
>>>>sheet, but nothing works.  The best scenario would be to select and
>>>>delete them but obviously this isn't possible.  Any thoughts?
>>>>
>>>>Thanks,
>>>>Louis
>>>>
>>>
>>>
>>
>>

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
1/9/2007 9:09:48 AM
Reply:

Similar Artilces:

Compile Error in hidden module: Autoexec #2
I need help when ever I opepen or close any office product ( excel , word etc...) I get the message shown above.... How do I fix it please help ag Take a look at this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 HTH Anders Silven "Ash" <ashrafg@sympatico.ca> skrev i meddelandet news:1574f01c446a0$4b5db2c0$a501280a@phx.gbl... > I need help when ever I opepen or close any office > product ( excel , word etc...) I get the message shown > above.... How do I fix it please help > > ag ...

AsyncFileUpload fails in hidden panel
Could someone help: I have the AsyncFileUpload controk within a <Panel> control that I toggle visibility True/False. For Example when the page loads pnlEdit.visible = False. Then when a user clicks on a detail record in a grid I display the panel with the AsyncFileControl ie: pnlEdit.Visible = True. Because the the AsyncFileUpload Control is not displayed on the page right away it errors when you try to upload a file. Would anyone know how I could preload the control or configure it to work in this configuration? ie: <asp:UpdatePanel ID="updEditDe...

unhidden columns keep hidden when opening file
When working in an Excel 2002 spreadsheet that had hidden columns. I unhide a column and enter info into that column. I then save and exit Excel. Upon opening of the spreadsheet again, that columns that I had unhidden are now hidden again. This happens not every time, but ofter enough to be problem. As a side note, the files all seem to be ones that were converted from Lotus. Any help would be appreciated. I've never seen this in xl2002. (But I don't have workbooks that were once Lotus 123 files, either.) Any chance you have a macro that adjusts the columnwidth? Randy Lyle ...

Calling Procedure in Hidden Form
What is the syntax for calling an event procedure in a hidden form from a visible (current) form? My hidden form has two procedures: form_open and Closebutton_click. I assume when I open the hidden form the form_open procedure is run. I want to run the Closebutton_click procedure in the hidden form when a certain event occurs on the visible (current) form. The CloseButton_click will run some code and then close the hidden form. On Wed, 7 Apr 2010 06:26:06 -0700, Stu <Stu@discussions.microsoft.com> wrote: You can make that procedure Public rather than the current P...

Form results set to go into hidden files - but aren't
Have an address collection page for building my newsletter base. The form is set to return results to hidden files (_private***) and also to email (FrontPage 2003). The email side works fine, but there's nothing going to the hidden files. Tried changing formats, etc, etc, etc but nothing. Whassup? Silly question: are you checking the hidden files on the server or on your PC? They will not be on your PC. A link to the form page will enable us to check the form properties. -- Ron Symonds Microsoft MVP (Expression Web) http://www.rxs-enterprises.org/fp/wf-menu.as...

How do I print only the hidden worksheets in a workbook?
One way: Sub PrintHiddenWS() Dim ws As Worksheet Dim i As Long Application.ScreenUpdating = False i = 0 For Each ws In ThisWorkbook.Worksheets With ws If .Visible = False Then i = i + 1 .Visible = True .PrintOut copies:=1, collate:=True .Visible = False End If End With Next Application.ScreenUpdating = True If i = 0 Then MsgBox "No hidden sheets found!" End If End Sub --- HTH Jason Atlanta, GA >-----Original Message----- > >. > ...

hidden received mail notification icon
The received mail (envelope) icon is hidden in the notification area in Outlook 2003. I'd like it to always appear when new unopened e-mail is present. Tools->options->email options->advanced e-mail options - check the box to show new mail notifications. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Stanley asked: | The received mail (envelope) icon is hidden in the | notifica...

Hidden data
Why does my chart disappear when I hide the data on the sheet? -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096 View this thread: http://www.excelforum.com/showthread.php?threadid=528675 Hi, If the actual chartobject disappears uncheck the move and size with cells property of the chartobject. If you data just disappears then with the chart selected use the menus Tools > Options > Chart > Plot Visible cells only. Cheers Andy Brisbane Rob wr...

hidden comments
I have a workbook on which I have been using comments. The comments are set to hidden (by right clicking on comment cell). Normally, I point to the cell with the red triangle at the top right and the comment shows as a cell tip. I have just added several more comments and can only see them if I right click and select to show comments which results in them being more permanently displayed. Is there a limit to the number of comments allowed on aworksheet? Have I pressed a key combination which has resulted in the comments not appearing when I hover the mouse over the triangles? Donn...

"Sand As" from hidden object
I've need to send messagge from varius source email, in example from user1@company1.com and user1@company2.com. I've created the account with e primary SMTP address (for company1) and a Distribution list with the second address (company2). If I Hide the DL the user can no more send the mail as company2, but if I unhide the DL in the GAL there is a number of unwanted list. There is a way to send a message from a DL not presente in GAL??? Otherwise there is another way to permit to an user tu use more then one address as sender (the limitation is the version of Outlook, that is the 200...

Excel 2000
Hi -- I have a user with a spreadsheet that will not display. I was able to Hide/Unhide it the other day, but now that is not working. I saved the file with another name with same result. I am able to open the file without a problem. Thanks for your help!! memcneely Maybe you unhid it, but it was off the visible screen. File|open the workbook window|unhide window|arrange|tiled (and resize by hand) and save it the way you like it. memcneely wrote: > > Hi -- > I have a user with a spreadsheet that will not display. I was able to > Hide/Unhide it the other day, but now that ...

hidden data in Excel 2007
I get messages about hidden modes when I open excel 2007. How do I unhide whatever the software is having trouble with? Yarmus It's likely a PDFMaker.xla add-in loaded by Adobe PDF writer software -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk "Yarmus" <Yarmus@discussions.microsoft.com> wrote in message news:35388A66-8920-48D8-A498-9027FAC87E46@microsoft.com... > I get messages about hidden modes when I open excel 2007. How do I unhide > whatever the software is having trouble with...

hidden form
When I use the database window to view the forms, some are missing! I think that somehow they are hidden from the database view, but I don't know how to unhide them. (Yes, I know they are there because they appear at certain points in running the database.) Thanks. -- Bill You don't mention which version of Access you are using. Depending on which, you can check a checkbox somewhere to "show hidden" objects. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post....

Cannot open any excel file when excel.exe is hidden in Task Manager.
I have about 60 users who are heavy Excel users. Every so often I get a call that they cannot open any Excel file. When I check it out I find that even though they do not have any Excel file listed in the taskbar they do have it listed under processes in the Task manager. When I end task on excel.exe they are then able to use Excel. I have seen this a number of different times, on different computers. Some are running Excel 2000 while others are using Excel XP. It does seem to happen more on XP. It also seems to happen more for people who use a lot of files at once. Does anyone know of...

How can I remove hidden apostrophe in Excel?
Hi, I have a spreadsheet of data and a number of the columns have data that is preceeded by a hidden apostrophe. The apostrophe can only be seen when you click on the cell. I have looked and can't find a post that addresses this. Can anyone out there tell me how to remove this. I have tried using the trim function in conjuction with the clean function and it didn't work. Ack! On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg <JimMoberg@discussions.microsoft.com> wrote: >Hi, > >I have a spreadsheet of data and a number of the columns have data that is >precee...

Hidden Recipients: Exchange 5.5 Administrator hangs.....
When I click on Hidden Recipients, the administrator hangs This is the first time I've been in it for ages. It doesn't matter whether you've selected mailboxes, custom recipients, all or whatever. Curious. I've got SP3 installed. Not SP4 - I'm loathe to do that in case I wreck it - I have no support for this any more and if I had to rebuild it I don't think I'd have the knowledge... Thanks in advance Martin does it hang when you're doing it on your workstation, or on the actual server? how long have you waited, to see if you get any error? "Mart...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

hidden text
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Someone has sent me a message on FirstClass indicating that there is a hidden message embedded within. I couldn't locate it so I pasted the message into Word (thinking that I might be able to locate it this way.) Nope. Can't find how to recover or discover hidden text. No idea -- I'd suggest you check the FirstClass site on how to use the product or get clarification from the sender. I doubt it has anything to do with Mac Office at all. Good Luck |:>) Bob Jones [MVP] Office:Mac On 1/23/10 12:13 PM, i...

Printing RTF text using a hidden CRichEditCtrl
Hi there, is it possible to print text using a hidden CRichEditCtrl? I was using some code from the article: http://www.codeproject.com/printing/richeditprint.asp In my application I do not always display the text. Sometimes I just need to put toghether some rtf text using template files and print the text. Here is the code I'm trying to make to work. I'm checking return values and cannot find any problems, except that the printer doesn't do anything. void CMainFrame::OnClickherePrint() { // Read In text. std::ifstream in( ".\\template.rtf" ); std::string st...

Toolbox hidden
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC For this last month now when I click on the Toobox icon to open it, it flies off screen to the right and I cannot find it. I tried re-installing Office 08...didn't help. I haven't had this problem before w/ Office 08. Any ideas? <br><br>Thanks Quit all Microsoft applications, then: Go to ~/Library/Preferences and delete com.microsoft.Excel.plist Then ~/Library/Preferences/Microsoft/Office 2008 and delete the whole folder. That will fix it. Re-installing very rarely fixes anything o...

How do delete Hidden Macros and excel links
Hi, I was wondering if anybody knew of an add-in that would delete hidden macros or excel links. I remember there being one but forgot where to download it for free. Thanks. Hi LT! To remove all code from a workbook: Chip Pearson: http://www.cpearson.com/excel/vbe.htm You need to look for the heading: "Deleting All VBA Code In A Project " To remove links you can use freely downloadable Addin FindLink from: Steve Bullen hosted: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel...

hidden rows can't be "unhid"
rows 1 and 2 on the spreadsheet do not appear in the screen - eventhough they can be accessed by cursor or "go to" commands. No amount of format,row,unhide commands seem to work. The row height is correct (not too small to see) What now coach? Thanks Maybe you're using Window|Freeze panes. And the rows are actually unhidden, but you can't scroll up to see them. Try turning it off (well, if it was on). jtboyt wrote: > > rows 1 and 2 on the spreadsheet do not appear in the > screen - eventhough they can be accessed by cursor or "go > to" command...

Outlook error "Compile error in hidden module: AutoExec"
I have just reinstalled Office XP Pro on my pc as I have just upgraded to WinXP. I noticed whenever I opened Outlook now, I get this message "Compile error in hidden module: AutoExec". I could not find the appropriate info relating to this on Help etc. Can someine please help? Thanks? Hi Herbert, do you use Word as Email editor? If so please read this http://support.microsoft.com/default.aspx?scid=kb;EN-US;307410 -- Gestern gings noch, ICH habe nichts gemacht! ;-) Bravestar@Datenschutzministerium.de "Herbert" <anonymous@discussions.microsoft.com> schrieb im New...

excel 2003 error>> compile error in hidden module: ThisWorkbook
Hi, I created this worksheet in Office XP. Now I have a new compute with WindowsXP pro and Office 2003. This worksheet opens fine on othe machines but on mine I get "compile error in hidden module: ThisWorkbook" When I look at it in VBA and run it , it will stop on this line. Else response = MsgBox("This is my message to the user.", _ vbOKCancel) If response = vbOK Then What is different in Office 2003 to make it have an error now -- Message posted from http://www.ExcelForum.com Hi Clayton, > Hi, I created this worksheet in Office XP. Now I have a new computer ...

irritating hidden hotkey
Hi, I am experimenting with hotkeys in Excel. Most of the time a key-combination doesn't result immediately in something visible or handy. But now I have come across a hot that is really disturbing. By pressing the tab-key excel normally selects the next column. Now, however excel selects the first column out of the monitor-window. So for instance from column to P. I don't remember the key combination and I have not found any option where to set the tab-jump tot devault. please help. Tools>Options>Transition. Uncheck "Transition navigation keys" Gord Dibben Ex...