Fixed objects will move

[staticbob]

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

 

[Raybhoy]

Although some of these post are quite old, I found the information to be spot on. What I did notice was that where I had written code for a particular scenario/filter criteria the warning did not come up. It was only when I tried to manually filter that I got the message. TVM -Raybhoy

 

[LeeSail]

Yes, ditto for me Greg. It worked for me too and saved the day.

 

[twobob10]

updating. Microsoft have also noted this issue with both 2010 and excel 2013. That have issued a macro to help resolve the issue in the mean time.

"Fixed objects will move" notice comes up repeatedly when filtering a range

 

[AltNrg4U]

I too must thank Greg for his wonderful help. I was stymied and tried many different things. I had a spreadsheet with over 115,000 records. It would have taken me a great deal of time to recreate. Much obliged!

 

[lance01]

There is an easier way,

Unprotect sheet
press F5
select special
select comments
click ok
It will find the cell right click only then press delete comment but it will delete all comments not just the initially found one. Faster and not macro experience neeeded.

 

[lance01]

If you want to use a macro for the sake of looking cool...

Sub ClearAllComments()
' Change Sheet 1 to your sheet
Sheets("Sheet1").Select

ActiveSheet.Unprotect
'or
'ActiveSheet.Unprotect Password:="Password"
Selection.SpecialCells(xlCellTypeComments).Select
Selection.ClearComments
End Sub

This will erase all coment and will fix the issue.

 

[kolangarakath]

Indeed a life saver, I had a workbook with 12 sheets, and the "Fixed objects will move" was freezing one of the sheets with 76000 rows of data. This tip worked, thanks much!!

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

 

[tcalleia]

Fabulous Greg Truby- 9 years later you saved the day for me!

 

[tapeinophrosun]

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...

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.

Thanks for being so so helpful......Thanks a bunch!!!!!!