Fixed objects will move

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Hi Guys,

I'm having problems when trying to apply an auto filter to my data. When I select the critieria from the auto-filter drop down, on any field heading, I get this messgae "Fixed Objects Will Move" about 18 times.

Any ideas ?

Cheers
Bob
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Yes, lots of the cells have comments Juan. Is there any way around this ? The comments are a fundamental part of the sheet.

Thanks
Bob
 
Upvote 0
Well, I have never encountered that error myself. But some Google searchs pointed out that comments (or shapes for that matter) can cause that under some circumstances... unfortunately, the given solution was "Delete all shapes and see if that works..."
 
Upvote 0
Thanks for the tips guys,

It seems that this is linked to the freeze panes option. If I un-freeze panes and apply filters the error does not occur.

Is there any way to code this ? So when a filter criteria is selected, remove freeze panes, apply the filter then re-freeze panes ?

Thanks,
Bob
 
Upvote 0
For me it really was due to comments, nothing to do with Freezing Panes. I was able to fix it with a bit of VBA...

Rich (BB code):
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

My thanks to Juan Pablo and Debra Dalgleish for providing the trail of breadcrumbs I needed to solve my (actually our Finance Dept's) problem.
 
Upvote 0
Excel: Fixed objects will move

Apparently occurs when using autofilter

Possible cause:
There are columns without headers and not within the filter range that contain formatting or comments

Possible solution:
remove all filtering
unhide all columns
select all unused columns
remove formatting from all unused columns
delete all unused columns
reapply filtering
 
Upvote 0
Hi,

I too faced the same problem while in Auto Filter mode.
Just try the below steps:
1) Select and delete the Rows from the last data available row till 65536 rows in excel
2) Select the last data available column and till to Last column of excel (Example: IV) and delete it.
3) Now save it and try auto filter.

The problem got solved for me. Hope it works for you. Enjoy

Regards
AG
 
Upvote 0
What a life saver :O) Greg saved the day!
This problem rendered my sheet useless; 12500 rows 50 columns 2500 comments; ran the macro 5 minutes and the autofilter was working again :0) joy.
The filter also ran much faster however comments adjacent to hidden lines were not visible you could only see the indicatior line - changed "xlMoveAndSize" to "xlmove" ran the macro again problems solved.

Cannot say thanks enough !!!
 
Upvote 0
Thanks much to Greg. I've been researching and struggling with this issue for months and his was the first suggested solution that actually worked. What I especially appreciate was that instead of being speculative, it was specific. It correctly identified the nature of the problem and followed up with a detailed, specific and correct solution. Not only that, the code contained no errors!

Bravo.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top