How to make the Excel broken autofilter indicator go away?

Excel 2003 Working with a large sheet which is typically autofiltered. However when autofilter is tu开发者_运维问答rned off the indicators are still all there, they just don't work. Not sure whether some vba was run which may have caused this.

Other workbooks behave correctly on the same machine, and this workbook behaves this way on other machines.

Has anyone ever run into this? Or better yet fixed it?

I just noticed the filtered range indicators went 45 columns right with no content on the last two column headers, although it was not actually filtered. I added content to those last two header columns and tried to run AutoFilteredMode=False, with no result, but then VisibleDropDown:=False later did hide the indicators, but only on the last two columns.

---

I would say that this is an issue left over from a Macro that did not complete correctly. I would start by running this macro to see if it fixes the issue.

Sub TurnFilterOff()
'removes AutoFilter if one exists
  Worksheets("Data").AutoFilterMode = False
End Sub 

If this does not work, you could do this to atleast hide the indicators.

Sub HideArrows() 
'hides all arrows 
Dim c As Range 
Dim i As Integer 
i = Cells(1, 1).End(xlToRight).Column 
Application.ScreenUpdating = False 

For Each c In Range(Cells(1, 1), Cells(1, i))
  c.AutoFilter Field:=c.Column, _ 
    Visibledropdown:=False 

Next 

Application.ScreenUpdating = True 
End Sub 

---

Finally I figured out that these residual shapes can be removed which then causes autofilter to behave correctly once you do that. Afterwards autofilter will turn on and off normally.

What caused the issues initially is a mystery to me. This will correct it, if anyone ever has the issue

Sub fixAutoFilterDisplay()
Dim drpdwn As Excel.Shape
For Each drpdwn In ActiveSheet.Shapes
  If InStr(drpdwn.Name, "Drop Down") <> 0 Then
   ' test1st = test1st & vbCrLf & drpdwn.Name  '''I tested this first
   drpdwn.Delete
 End If
 Next
   'MsgBox (test1st)
 End Sub