Hide all autofilter dropdown arrows
 

I have a macro that sets an autofilter range with criteria set for column A.
I can hide the drop down arrow in A with:

Worksheets("Jobs").Range("A3:P3")
.AutoFilter _
field:=1, _
Criteria1:="Y", _
visibledropdown:=False

Is there a way to hide all dropdown arrows in the range?

Hope you can help.

Jim
--
Jim

Hide all autofilter dropdown arrows
 

I don't think so, but why don't you apply Autofilter only to range("A3:A?")?
Regards,
Stefi

€žJim G€� ezt Ã*rta:

I have a macro that sets an autofilter range with criteria set for column A.
I can hide the drop down arrow in A with:

Worksheets("Jobs").Range("A3:P3")
.AutoFilter _
field:=1, _
Criteria1:="Y", _
visibledropdown:=False

Is there a way to hide all dropdown arrows in the range?

Hope you can help.

Jim
--
Jim

Hide all autofilter dropdown arrows
 

Thanks Stefi,
After all these years of habitually selecting the first row of the data
block to autofilter, it never occured to me that I only needed the first
cell! Thank you very much for the insight.

For anyone interested here is the final result:

Sub FilterActive()
'
' Filter Macro to show only active jobs
'
Dim LastRow As Long

Application.ScreenUpdating = False

With Worksheets("Jobs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'
With ActiveSheet
If .AutoFilterMode Then
.Range("$A$3:$A" & LastRow).AutoFilter field:=1, Criteria1:="Y",
visibledropdown:=False
Else

Range("A3").Select
.Range("$A$3:$A" & LastRow).AutoFilter field:=1, Criteria1:="Y",
visibledropdown:=False
End If
End With

Range("A3").Select

RowFit 'macro to autofit rows to data height

Application.ScreenUpdating = True
End Sub

I dare say I don't even need the 'Lastrow' variable now.

--
Jim


"Stefi" wrote:

I don't think so, but why don't you apply Autofilter only to range("A3:A?")?
Regards,
Stefi

€žJim G€� ezt Ã*rta:

I have a macro that sets an autofilter range with criteria set for column A.
I can hide the drop down arrow in A with:

Worksheets("Jobs").Range("A3:P3")
.AutoFilter _
field:=1, _
Criteria1:="Y", _
visibledropdown:=False

Is there a way to hide all dropdown arrows in the range?

Hope you can help.

Jim
--
Jim

Hide all autofilter dropdown arrows
 

You are welcome! Thanks for the feedback!
Stefi

€žJim G€� ezt Ã*rta:

Thanks Stefi,
After all these years of habitually selecting the first row of the data
block to autofilter, it never occured to me that I only needed the first
cell! Thank you very much for the insight.

For anyone interested here is the final result:

Sub FilterActive()
'
' Filter Macro to show only active jobs
'
Dim LastRow As Long

Application.ScreenUpdating = False

With Worksheets("Jobs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'
With ActiveSheet
If .AutoFilterMode Then
.Range("$A$3:$A" & LastRow).AutoFilter field:=1, Criteria1:="Y",
visibledropdown:=False
Else

Range("A3").Select
.Range("$A$3:$A" & LastRow).AutoFilter field:=1, Criteria1:="Y",
visibledropdown:=False
End If
End With

Range("A3").Select

RowFit 'macro to autofit rows to data height

Application.ScreenUpdating = True
End Sub

I dare say I don't even need the 'Lastrow' variable now.

--
Jim


"Stefi" wrote:

I don't think so, but why don't you apply Autofilter only to range("A3:A?")?
Regards,
Stefi

€žJim G€� ezt Ã*rta:

I have a macro that sets an autofilter range with criteria set for column A.
I can hide the drop down arrow in A with:

Worksheets("Jobs").Range("A3:P3")
.AutoFilter _
field:=1, _
Criteria1:="Y", _
visibledropdown:=False

Is there a way to hide all dropdown arrows in the range?

Hope you can help.

Jim
--
Jim

Hide all autofilter dropdown arrows
 

I'm not quite sure why you'd want that, but...
http://contextures.com/xlautofilter03.html#Hide
(from Debra Dalgleish's site)

This kind of thing would remove the arrows from the existing autofilter range:

Dim c As Range
For Each c In ActiveSheet.AutoFilter.Range.Rows(1).Cells
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next c



Jim G wrote:

I have a macro that sets an autofilter range with criteria set for column A.
I can hide the drop down arrow in A with:

Worksheets("Jobs").Range("A3:P3")
.AutoFilter _
field:=1, _
Criteria1:="Y", _
visibledropdown:=False

Is there a way to hide all dropdown arrows in the range?

Hope you can help.

Jim
--
Jim

--

Dave Peterson