Turn AutoFilters Off

[Jasmine]

I have a worksheet that may or may not have the AutoFilters turned on. I need
to have the filters off before I run the macro I have written. How can I
incorporate this into my macro? Thanks!

[Gixxer_J_97]

if you mean having each filter set to show 'all' then something like this
might help

With Sheets("MySheet")
For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
.Rows(1).AutoFilter field:=i
Next i
End With

change the argument of .Rows() to match what row your filters are in


"Jasmine" wrote:

> I have a worksheet that may or may not have the AutoFilters turned on. I need
> to have the filters off before I run the macro I have written. How can I
> incorporate this into my macro? Thanks!

[Jasmine]

I actually mean the the drop-down arrows for the filters are on and I need
them to be off.

"Gixxer_J_97" wrote:

> if you mean having each filter set to show 'all' then something like this
> might help
>
> With Sheets("MySheet")
> For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> .Rows(1).AutoFilter field:=i
> Next i
> End With
>
> change the argument of .Rows() to match what row your filters are in
>
>
> "Jasmine" wrote:
>
> > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > to have the filters off before I run the macro I have written. How can I
> > incorporate this into my macro? Thanks!

[Gixxer_J_97]

Right, this code should reset each of the drop-down auto-filters in .rows(X)
to display 'All' records in that row, thus turning them off.

Ie if column B contains month names (Jan, Feb, etc etc) and you had it set
to only show January, this code will reset the filter to show them all (ie
they're now off)


"Jasmine" wrote:

> I actually mean the the drop-down arrows for the filters are on and I need
> them to be off.
>
> "Gixxer_J_97" wrote:
>
> > if you mean having each filter set to show 'all' then something like this
> > might help
> >
> > With Sheets("MySheet")
> > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > .Rows(1).AutoFilter field:=i
> > Next i
> > End With
> >
> > change the argument of .Rows() to match what row your filters are in
> >
> >
> > "Jasmine" wrote:
> >
> > > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > > to have the filters off before I run the macro I have written. How can I
> > > incorporate this into my macro? Thanks!

[Gixxer_J_97]

or did you mean you wanted to completely remove the autofilter? (so that the
drop-down boxes are no longer there)?


"Jasmine" wrote:

> I actually mean the the drop-down arrows for the filters are on and I need
> them to be off.
>
> "Gixxer_J_97" wrote:
>
> > if you mean having each filter set to show 'all' then something like this
> > might help
> >
> > With Sheets("MySheet")
> > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > .Rows(1).AutoFilter field:=i
> > Next i
> > End With
> >
> > change the argument of .Rows() to match what row your filters are in
> >
> >
> > "Jasmine" wrote:
> >
> > > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > > to have the filters off before I run the macro I have written. How can I
> > > incorporate this into my macro? Thanks!

[Gixxer_J_97]

if you want to remove auto-filter, then
> > With Sheets("MySheet")
> > .Rows(1).AutoFilter
> > End With
should work

hth

J

"Jasmine" wrote:

> I actually mean the the drop-down arrows for the filters are on and I need
> them to be off.
>
> "Gixxer_J_97" wrote:
>
> > if you mean having each filter set to show 'all' then something like this
> > might help
> >
> > With Sheets("MySheet")
> > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > .Rows(1).AutoFilter field:=i
> > Next i
> > End With
> >
> > change the argument of .Rows() to match what row your filters are in
> >
> >
> > "Jasmine" wrote:
> >
> > > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > > to have the filters off before I run the macro I have written. How can I
> > > incorporate this into my macro? Thanks!

[Jasmine]

Yes. I need to completely remove the filters.

"Gixxer_J_97" wrote:

> or did you mean you wanted to completely remove the autofilter? (so that the
> drop-down boxes are no longer there)?
>
>
> "Jasmine" wrote:
>
> > I actually mean the the drop-down arrows for the filters are on and I need
> > them to be off.
> >
> > "Gixxer_J_97" wrote:
> >
> > > if you mean having each filter set to show 'all' then something like this
> > > might help
> > >
> > > With Sheets("MySheet")
> > > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > > .Rows(1).AutoFilter field:=i
> > > Next i
> > > End With
> > >
> > > change the argument of .Rows() to match what row your filters are in
> > >
> > >
> > > "Jasmine" wrote:
> > >
> > > > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > > > to have the filters off before I run the macro I have written. How can I
> > > > incorporate this into my macro? Thanks!

[Dave Peterson]

worksheets("Sheet1").autofiltermode=false

is one way.


Jasmine wrote:
>
> I actually mean the the drop-down arrows for the filters are on and I need
> them to be off.
>
> "Gixxer_J_97" wrote:
>
> > if you mean having each filter set to show 'all' then something like this
> > might help
> >
> > With Sheets("MySheet")
> > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > .Rows(1).AutoFilter field:=i
> > Next i
> > End With
> >
> > change the argument of .Rows() to match what row your filters are in
> >
> >
> > "Jasmine" wrote:
> >
> > > I have a worksheet that may or may not have the AutoFilters turned on. I need
> > > to have the filters off before I run the macro I have written. How can I
> > > incorporate this into my macro? Thanks!

--

Dave Peterson

[Tom Ogilvy]

Activesheet.AutoFilterMode = False

works whether there is an autofilter on the sheet or not.

--
Regards,
Tom Ogilvy

"Jasmine" <Jasmine@discussions.microsoft.com> wrote in message
news:23CC71AC-6B5F-43C6-8D4C-57FDB502842C@microsoft.com...
> I actually mean the the drop-down arrows for the filters are on and I need
> them to be off.
>
> "Gixxer_J_97" wrote:
>
> > if you mean having each filter set to show 'all' then something like
this
> > might help
> >
> > With Sheets("MySheet")
> > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > .Rows(1).AutoFilter field:=i
> > Next i
> > End With
> >
> > change the argument of .Rows() to match what row your filters are in
> >
> >
> > "Jasmine" wrote:
> >
> > > I have a worksheet that may or may not have the AutoFilters turned on.
I need
> > > to have the filters off before I run the macro I have written. How can
I
> > > incorporate this into my macro? Thanks!

[Gixxer_J_97]

Dave & Tom - would that reset them all to show 'all' items in the column, or
remove the autofilter from the sheet?

thx!

J

"Tom Ogilvy" wrote:

> Activesheet.AutoFilterMode = False
>
> works whether there is an autofilter on the sheet or not.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jasmine" <Jasmine@discussions.microsoft.com> wrote in message
> news:23CC71AC-6B5F-43C6-8D4C-57FDB502842C@microsoft.com...
> > I actually mean the the drop-down arrows for the filters are on and I need
> > them to be off.
> >
> > "Gixxer_J_97" wrote:
> >
> > > if you mean having each filter set to show 'all' then something like
> this
> > > might help
> > >
> > > With Sheets("MySheet")
> > > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > > .Rows(1).AutoFilter field:=i
> > > Next i
> > > End With
> > >
> > > change the argument of .Rows() to match what row your filters are in
> > >
> > >
> > > "Jasmine" wrote:
> > >
> > > > I have a worksheet that may or may not have the AutoFilters turned on.
> I need
> > > > to have the filters off before I run the macro I have written. How can
> I
> > > > incorporate this into my macro? Thanks!
>
>
>

[Tom Ogilvy]

Remove them from the sheet.

--
Regards,
Tom Ogilvy


"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:54199583-F7BC-4EA7-AB29-091BE94B3EB9@microsoft.com...
> Dave & Tom - would that reset them all to show 'all' items in the column,
or
> remove the autofilter from the sheet?
>
> thx!
>
> J
>
> "Tom Ogilvy" wrote:
>
> > Activesheet.AutoFilterMode = False
> >
> > works whether there is an autofilter on the sheet or not.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jasmine" <Jasmine@discussions.microsoft.com> wrote in message
> > news:23CC71AC-6B5F-43C6-8D4C-57FDB502842C@microsoft.com...
> > > I actually mean the the drop-down arrows for the filters are on and I
need
> > > them to be off.
> > >
> > > "Gixxer_J_97" wrote:
> > >
> > > > if you mean having each filter set to show 'all' then something like
> > this
> > > > might help
> > > >
> > > > With Sheets("MySheet")
> > > > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > > > .Rows(1).AutoFilter field:=i
> > > > Next i
> > > > End With
> > > >
> > > > change the argument of .Rows() to match what row your filters are in
> > > >
> > > >
> > > > "Jasmine" wrote:
> > > >
> > > > > I have a worksheet that may or may not have the AutoFilters turned
on.
> > I need
> > > > > to have the filters off before I run the macro I have written. How
can
> > I
> > > > > incorporate this into my macro? Thanks!
> >
> >
> >

[Tom Ogilvy]

And if there isn't one, that will add one. Don't believe that is what he
wants.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:5824E9D7-D20B-4F79-82EA-26900008A8C4@microsoft.com...
> if you want to remove auto-filter, then
> > > With Sheets("MySheet")
> > > .Rows(1).AutoFilter
> > > End With
> should work
>
> hth
>
> J
>
> "Jasmine" wrote:
>
> > I actually mean the the drop-down arrows for the filters are on and I
need
> > them to be off.
> >
> > "Gixxer_J_97" wrote:
> >
> > > if you mean having each filter set to show 'all' then something like
this
> > > might help
> > >
> > > With Sheets("MySheet")
> > > For i = 1 To Sheets("MySheet").UsedRange.Columns.count '- 1
> > > .Rows(1).AutoFilter field:=i
> > > Next i
> > > End With
> > >
> > > change the argument of .Rows() to match what row your filters are in
> > >
> > >
> > > "Jasmine" wrote:
> > >
> > > > I have a worksheet that may or may not have the AutoFilters turned
on. I need
> > > > to have the filters off before I run the macro I have written. How
can I
> > > > incorporate this into my macro? Thanks!