VBA showing drop down in filter

[judgejustin]

When I run this macro it is still showing the drop down box in my headers. I do not want it to. Any ideas?

Private Sub Worksheet_Activate()
   With Sheets("Statistics")
   .Unprotect "fldorcusreport"
   Me.Range("D1").AutoFilter 4, "ACTIVE", xlOr, "RESERVE", VisibleDropDown:=False

   .Protect "fldorcusreport", UserInterFaceOnly:=True
End With
End Sub

 

[Fluff]

How about

Range("D1", Range("D" & Rows.Count).End(xlUp)).AutoFilter 4, "ACTIVE", xlOr, "RESERVE", False

 

[judgejustin]

I get an error when I use that. It says
AutoFilter Method of Range class failed

 

[Fluff]

Oops, it should be

   Range("D1", Range("D" & Rows.Count).End(xlUp)).AutoFilter 1, "ACTIVE", xlOr, "RESERVE", False

 

[judgejustin]

That takes away the drop downs but it is not filtering the data any longer. If I change it back to .AutoFilter 4, I get another runtime error.

 

[Fluff]

Do you have filters applied to any other columns?

 

[judgejustin]

No only in D

 

[judgejustin]

I think it may be working ok
I just copied a new sheet that has not been used before and did a quick test and it seems like it may be working. It will take me a little while to do the entire thing thought

 

[Fluff]

Ok, how about

Private Sub Worksheet_Activate()
   Dim i As Long

   Me.Unprotect "fldorcusreport"
   If Me.AutoFilterMode = False Then Range("D1").AutoFilter
   With Me.AutoFilter.Filters
      For i = 1 To .Count
         If i = 4 Then
            .Parent.Range.AutoFilter 4, "ACTIVE", xlOr, "RESERVE", False
         Else
            .Parent.Range.AutoFilter i, , , , False
         End If
      Next i
   End With
   Me.Protect "fldorcusreport", UserInterFaceOnly:=True
End Sub

 

[judgejustin]

The other is working. I just completed a decent test of it and it all seems fine.
I'm going to copy this code you just did though in case something doesn't work out and I can try it.

Thanks a lot