Solved: How do you eliminate the dropdown arrow in my autofilter code?

[genracela]

This is in relation to:
http://www.vbaexpress.com/forum/showthread.php?t=32218

I have an autofilter code and I want to the filter dropdown to be invisible.

How do I do that?


Sub FilterB7()
If UCase(Range("B6").Value) <> "ALL" Then
Range("A6").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").TextBox1.Value
Else
If ActiveSheet.AutoFilterMode Then
Range("A7").AutoFilter
End If
End If
Range("A1").Select
End Sub

Sub ClearFilter()
If ActiveSheet.AutoFilterMode Then
Range("A6").AutoFilter Field:=2, Criteria1:=""
End If
Sheets("Sheet1").TextBox1.Value = ""
Range("A1").Select
End Sub


Thanks!

[mbarron]

You'll have to use the Advanced Filter method:

Dim rng As Range
If UCase(Range("B6").Value) <> "ALL" Then
Range("P1") = Range("B6")
Range("P2") = Sheets("Sheet1").TextBox1.Value

Set rng = Range("A6").CurrentRegion
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("P1:P2"), Unique:=False

Range("P1:P2").ClearContents
End If
End Sub


Sub ClearFilter()

On Error Resume Next
ActiveSheet.ShowAllData
Sheets("Sheet1").TextBox1.Value = ""

End Sub

[starsky]

Is this what you're after?
.AutoFilter Field:=1, VisibleDropDown:=True

Or false.

On Datapig blog today by coincidence.

[mbarron]

Using the VisibleDropDown = False argument only hides the Dropdowns for the filtered fields, not all fields.

[p45cal]

you can try this on autofilter (works here on xl2007 (compatibility mode)) on your old sheet from the other thread:
Sub FilterB6()
Dim fld
Application.ScreenUpdating = False
If UCase(Range("B6").Value) <> "ALL" Then
Range("A6:N6").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").TextBox1.Value, visibledropdown:=False
With ActiveSheet.AutoFilter
For fld = 1 To 14
If Not .Filters(fld).On Then
Range("A6:N6").AutoFilter Field:=fld, visibledropdown:=False
End If
Next fld
End With
Else
If ActiveSheet.AutoFilterMode Then
Range("A6:N6").AutoFilter
End If
End If
Application.ScreenUpdating = True
End Sub

[genracela]

Thanks guys for the help!

I used both code from mbarron and p45cal, and it both worked!