Thanks Asa I have a column without heading. I will check and get back to you soon! Sandeep Chhajer. Sent on my BlackBerry® from Vodafone
-----Original Message----- From: "Asa Rossoff" <a...@lovetour.info> Sender: excel-macros@googlegroups.com Date: Tue, 6 Mar 2012 12:19:29 To: <excel-macros@googlegroups.com> Reply-To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Unique problem Dear Sandeep, My first thought was you may need to set objects on your worksheet to move and size with cells, or at least move with them (i.e. make your objects no longer fixed)... and I just did a web search (yes, google/yahoo/msn is your friend) and came up with several issues that can cause this error under certain circumstances: 1. Comments with fixed position/size 2. Window/Freeze panes enabled 3. "There are columns without headers and not within the filter range that contain formatting or comments" Things you can try for resolving the issue, based on http://support.microsoft.com/default.aspx?id=211769 and online comments: 1. Highlight all rows below your table to the max (65536 or 1000000000 or whatever depending on Excel version) and right-click, delete. Same for all unused columns. 2. Turn off Window-Freeze panes. 3. In Excel 2007+ temporarily toggle the option from "Nothing (hide objects)" to All a. Office button b. Excel Options c. Advanced d. scroll down to Display options for this workbook section e. click All under For objects, show:. f. click OK. 4. Change the position property of comments/objects to "Move and size with cells" (or possibly just "Move with cells" may also work and give a better result in a filtered list or for if you resize rows columns later): a. Manual method for comments: see "Method 2" in linked Microsoft KB article. b. Automatic method. Use a VBA macro; i. Change all objects on active sheet to move and size with cells (credit Microsoft KB): Sub Test() Dim s As Shape On Error Resume Next For Each s In ActiveSheet.Shapes s.Placement = xlMoveAndSize Next End Sub You could also try your luck at changing the s.Placement line to "s.Placement = xlMove" if some comments or other objects you wish to be fully visible are not due to adjacent hidden rows after running the above macro. ii. Change comments only on active sheet to move and size with cells (credit Greg Truby): Sub CommentsMoveAndSize() '// Written by Greg Truby, July 2006 '// '// If a large worksheet has comments and you '// attempt to apply AutoFilter, sometimes Excel will '// start telling you "Fixed objects will move." '// '// Per the kb article here: '// «« http://support.microsoft.com/default.aspx?id=211769 »» '// this routine fixes it. '// '// [Tested on a worksheet with 5,000 rows × 150 columns '// and 1,796 comments. Also tested on shared workbooks, '// and protected worksheets. Neither raised an error.] Dim cmtEach As Comment If ActiveSheet.Type <> XlSheetType.xlWorksheet Then MsgBox "What are you? Daft?" & vbCr & vbCr & "Pick a worksheet.", _ vbExclamation, "Bad Sheet Selection" Exit Sub ElseIf ActiveSheet.Comments.Count = 0 Then MsgBox "There are no comments on the active sheet.", vbInformation, "I Have No Comment" Else '// Change each comment to "move and size with cells" For Each cmtEach In ActiveSheet.Comments cmtEach.Shape.Placement = xlMoveAndSize Next cmtEach End If End Sub You could likewise in this procedure also try your luck at changing "cmtEach.Shape.Placement = xlMoveAndSize" to "cmtEach.Shape.Placement = xlMove" to ensure comments will always be showable even with adjacent hidden rows. Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Tuesday, March 06, 2012 11:07 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Unique problem Hello Sandeep; Do you have any shapes, images, or controls on the worksheet? -----Original Message----- From: <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com [ <mailto:excel-macros@googlegroups.com> mailto:excel-macros@googlegroups.com] On Behalf Of <mailto:chhajersand...@gmail.com> chhajersand...@gmail.com Sent: Tuesday, March 06, 2012 5:38 AM To: <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Unique problem Dear Excel gurus, I am facing a unique problem. I have a file which has been filtered. Now when I am trying see all data..it is giving me a message saying.."fixed objects will move". I am clicking okay...but getting no result as it is appearing again and again. I have closed the file forcefully and reopened it but no avail. Please help. Thanking you in advance. Sandeep Chhajer. Sent on my BlackBerryR from Vodafone -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
