[RESOLVED] advancedfilter - arrows being removed

[scoobster]

Hi

I have a table range called 'picklist' and I want to do an advanced filter. All that works fine but when the code hits the .advancedfilter line then the filter arrows are removed. I could put this back but this is the only place in the entire program that this happens - other advancedfilters work fine.

Any clue why this happens and how to stop it happening?

Private Sub cmdExport_Click()

Dim wbSaveAs As String
Dim wbSaveName As String
Dim newWorkBook As Workbook
Dim rng As Range
Dim oSh As Worksheet
Set oSh = worksheets("sheet1")

wbSaveName = worksheets("sheet4").Range("F4").Value
wbSaveAs = worksheets("sheet4").Range("F3").Value & wbSaveName

worksheets("sheet1").Unprotect

Set rng = worksheets("sheet1").ListObjects("PickList").AutoFilter.Range

     With worksheets("sheet1").Range("Picklist[#All]")
                      
        Call FreezeScreen
           
Action:=xlFilterInPlace, CriteriaRange:=worksheets("sheet4").Range("H1:J3"), Unique:=False
        oSh.ListObjects("PickList").Range.advancedfilter Action:=xlFilterInPlace, CriteriaRange:=worksheets("sheet4").Range("H1:J3"), Unique:=False

I'm calling 'freezescreen' and this is the code;

Sub FreezeScreen()

    With Application
'        xlCalc = .Calculation
'        .Calculation = xlCalculationManual
'        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

End Sub

[vbfbryce]

One way I've gotten around this in the past is to have two different criteria ranges. One would have "NO" as the only criterion, the other would have "YES" and "NO" both, so the arrows would stay there, but all records would be displayed (or whatever your specific criteria might be).

[scoobster]

Thanks

So the arrows going missing is a feature of the advancedfilter command and is not something I'm doing wrong? If it's the latter then I can work around it somehow in the knowledge I'm using the command correctly.

[vbfbryce]

I can't say for sure it's NOT you!

The only time I've encountered it is when I "filter with no criteria" to make all the records visible again. I've never had it happen when I actually DID filter.