Selection.AutoFilter Field:=1

MBlake · 08-25-2005, 11:05 AM

Hi,
I need to turn off the auto filter option and ensure any filtered columns
are returned to non-filter state prior to running some VBA, at present I am
using Selection.AutoFilter Field:=1 and repeating it for each of the umpteen
columns. This looks rather messy and adds a lot of lines to code. I am
sure it can be scaled down to one line, however so far I haven't managed it.
I have tried a few ideas to no avail -

I'd appreciate any advice on this one,
Best Wishes,
Mickey

-------------------------------------------------------------------------
Currently Using -
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4 etc.
-------------------------------------------------------------------------
Have tried -

Sub Macro2()
'
Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5,
Field:=6
End Sub
--------------------------------------------------------------------------
Sub autofilter_off()
With ActiveSheet
.EnableAutoFilter = True
End With
End Sub
-------------------------------------------------------------------------

Ron de Bruin · 08-25-2005, 12:05 PM

Hi Mickey

ActiveSheeet.AutoFilterMode = False

--
Regards Ron de Bruin
http://www.rondebruin.nl

"MBlake" <[email protected]> wrote in message news:[email protected]...
> Hi,
> I need to turn off the auto filter option and ensure any filtered columns are returned to non-filter state prior to running some
> VBA, at present I am using Selection.AutoFilter Field:=1 and repeating it for each of the umpteen columns. This looks rather
> messy and adds a lot of lines to code. I am sure it can be scaled down to one line, however so far I haven't managed it. I have
> tried a few ideas to no avail -
>
> I'd appreciate any advice on this one,
> Best Wishes,
> Mickey
>
> -------------------------------------------------------------------------
> Currently Using -
> Selection.AutoFilter Field:=1
> Selection.AutoFilter Field:=2
> Selection.AutoFilter Field:=3
> Selection.AutoFilter Field:=4 etc.
> -------------------------------------------------------------------------
> Have tried -
>
> Sub Macro2()
> '
> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5, Field:=6
> End Sub
> --------------------------------------------------------------------------
> Sub autofilter_off()
> With ActiveSheet
> .EnableAutoFilter = True
> End With
> End Sub
> -------------------------------------------------------------------------
>
>

PCLIVE · 08-25-2005, 12:05 PM

Couldn't you just use the opposite function from your Sub autofilter_off()
macro by changing it to "False"?

Sub autofilter_off()
With ActiveSheet
.EnableAutoFilter = False
End With
End Sub

Just a thought.
Paul

"MBlake" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I need to turn off the auto filter option and ensure any filtered columns
> are returned to non-filter state prior to running some VBA, at present I
> am using Selection.AutoFilter Field:=1 and repeating it for each of the
> umpteen columns. This looks rather messy and adds a lot of lines to code.
> I am sure it can be scaled down to one line, however so far I haven't
> managed it. I have tried a few ideas to no avail -
>
> I'd appreciate any advice on this one,
> Best Wishes,
> Mickey
>
> -------------------------------------------------------------------------
> Currently Using -
> Selection.AutoFilter Field:=1
> Selection.AutoFilter Field:=2
> Selection.AutoFilter Field:=3
> Selection.AutoFilter Field:=4 etc.
> -------------------------------------------------------------------------
> Have tried -
>
> Sub Macro2()
> '
> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5,
> Field:=6
> End Sub
> --------------------------------------------------------------------------
> Sub autofilter_off()
> With ActiveSheet
> .EnableAutoFilter = True
> End With
> End Sub
> -------------------------------------------------------------------------
>
>

MBlake · 08-25-2005, 01:05 PM

Thanks Ron,
I am getting an error of 'Object Required' when I use it as either the
below - I normally use Option Explicit but if that is included I also get an
Variable Not Defined message.

I'd appreciate one more hint if possible.

Thanks,
Mickey

Sub autofilter_off()
With ActiveSheet
ActiveSheeet.AutoFilterMode = False
End With
End Sub
-----------------------------------------------
Sub autofilter_off()
ActiveSheeet.AutoFilterMode = False
End Sub
-----------------------------------------------

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Hi Mickey
>
> ActiveSheeet.AutoFilterMode = False
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "MBlake" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I need to turn off the auto filter option and ensure any filtered columns
>> are returned to non-filter state prior to running some VBA, at present I
>> am using Selection.AutoFilter Field:=1 and repeating it for each of the
>> umpteen columns. This looks rather messy and adds a lot of lines to
>> code. I am sure it can be scaled down to one line, however so far I
>> haven't managed it. I have tried a few ideas to no avail -
>>
>> I'd appreciate any advice on this one,
>> Best Wishes,
>> Mickey
>>
>> -------------------------------------------------------------------------
>> Currently Using -
>> Selection.AutoFilter Field:=1
>> Selection.AutoFilter Field:=2
>> Selection.AutoFilter Field:=3
>> Selection.AutoFilter Field:=4 etc.
>> -------------------------------------------------------------------------
>> Have tried -
>>
>> Sub Macro2()
>> '
>> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5,
>> Field:=6
>> End Sub
>> --------------------------------------------------------------------------
>> Sub autofilter_off()
>> With ActiveSheet
>> .EnableAutoFilter = True
>> End With
>> End Sub
>> -------------------------------------------------------------------------
>>
>>
>
>

MBlake · 08-25-2005, 01:05 PM

Hi and thanks for the reply,
Unfortunately I tried FALSE earlier but that also fails,

Mickey

"PCLIVE" <[email protected]> wrote in message
news:[email protected]...
> Couldn't you just use the opposite function from your Sub autofilter_off()
> macro by changing it to "False"?
>
> Sub autofilter_off()
> With ActiveSheet
> .EnableAutoFilter = False
> End With
> End Sub
>
>
> Just a thought.
> Paul
>
> "MBlake" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I need to turn off the auto filter option and ensure any filtered columns
>> are returned to non-filter state prior to running some VBA, at present I
>> am using Selection.AutoFilter Field:=1 and repeating it for each of the
>> umpteen columns. This looks rather messy and adds a lot of lines to
>> code. I am sure it can be scaled down to one line, however so far I
>> haven't managed it. I have tried a few ideas to no avail -
>>
>> I'd appreciate any advice on this one,
>> Best Wishes,
>> Mickey
>>
>> -------------------------------------------------------------------------
>> Currently Using -
>> Selection.AutoFilter Field:=1
>> Selection.AutoFilter Field:=2
>> Selection.AutoFilter Field:=3
>> Selection.AutoFilter Field:=4 etc.
>> -------------------------------------------------------------------------
>> Have tried -
>>
>> Sub Macro2()
>> '
>> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5,
>> Field:=6
>> End Sub
>> --------------------------------------------------------------------------
>> Sub autofilter_off()
>> With ActiveSheet
>> .EnableAutoFilter = True
>> End With
>> End Sub
>> -------------------------------------------------------------------------
>>
>>
>
>

Ron de Bruin · 08-25-2005, 01:05 PM

This will work

I delete one activesheet and two times a "e"

Sub autofilter_off()
With ActiveSheet
.AutoFilterMode = False
End With
End Sub
-----------------------------------------------
Sub autofilter_off()
ActiveSheet.AutoFilterMode = False
End Sub
-----------------------------------------------

--
Regards Ron de Bruin
http://www.rondebruin.nl

"MBlake" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron,
> I am getting an error of 'Object Required' when I use it as either the below - I normally use Option Explicit but if that is
> included I also get an Variable Not Defined message.
>
> I'd appreciate one more hint if possible.
>
> Thanks,
> Mickey
>
>
> Sub autofilter_off()
> With ActiveSheet
> ActiveSheeet.AutoFilterMode = False
> End With
> End Sub
> -----------------------------------------------
> Sub autofilter_off()
> ActiveSheeet.AutoFilterMode = False
> End Sub
> -----------------------------------------------
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> Hi Mickey
>>
>> ActiveSheeet.AutoFilterMode = False
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "MBlake" <[email protected]> wrote in message news:[email protected]...
>>> Hi,
>>> I need to turn off the auto filter option and ensure any filtered columns are returned to non-filter state prior to running some
>>> VBA, at present I am using Selection.AutoFilter Field:=1 and repeating it for each of the umpteen columns. This looks rather
>>> messy and adds a lot of lines to code. I am sure it can be scaled down to one line, however so far I haven't managed it. I have
>>> tried a few ideas to no avail -
>>>
>>> I'd appreciate any advice on this one,
>>> Best Wishes,
>>> Mickey
>>>
>>> -------------------------------------------------------------------------
>>> Currently Using -
>>> Selection.AutoFilter Field:=1
>>> Selection.AutoFilter Field:=2
>>> Selection.AutoFilter Field:=3
>>> Selection.AutoFilter Field:=4 etc.
>>> -------------------------------------------------------------------------
>>> Have tried -
>>>
>>> Sub Macro2()
>>> '
>>> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5, Field:=6
>>> End Sub
>>> --------------------------------------------------------------------------
>>> Sub autofilter_off()
>>> With ActiveSheet
>>> .EnableAutoFilter = True
>>> End With
>>> End Sub
>>> -------------------------------------------------------------------------
>>>
>>>
>>
>>
>
>

Ron de Bruin · 08-25-2005, 01:05 PM

Btw : Sorry for the typo (ActiveSheeet instead of ActiveSheet)

--
Regards Ron de Bruin
http://www.rondebruin.nl

"MBlake" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron,
> I am getting an error of 'Object Required' when I use it as either the below - I normally use Option Explicit but if that is
> included I also get an Variable Not Defined message.
>
> I'd appreciate one more hint if possible.
>
> Thanks,
> Mickey
>
>
> Sub autofilter_off()
> With ActiveSheet
> ActiveSheeet.AutoFilterMode = False
> End With
> End Sub
> -----------------------------------------------
> Sub autofilter_off()
> ActiveSheeet.AutoFilterMode = False
> End Sub
> -----------------------------------------------
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> Hi Mickey
>>
>> ActiveSheeet.AutoFilterMode = False
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "MBlake" <[email protected]> wrote in message news:[email protected]...
>>> Hi,
>>> I need to turn off the auto filter option and ensure any filtered columns are returned to non-filter state prior to running some
>>> VBA, at present I am using Selection.AutoFilter Field:=1 and repeating it for each of the umpteen columns. This looks rather
>>> messy and adds a lot of lines to code. I am sure it can be scaled down to one line, however so far I haven't managed it. I have
>>> tried a few ideas to no avail -
>>>
>>> I'd appreciate any advice on this one,
>>> Best Wishes,
>>> Mickey
>>>
>>> -------------------------------------------------------------------------
>>> Currently Using -
>>> Selection.AutoFilter Field:=1
>>> Selection.AutoFilter Field:=2
>>> Selection.AutoFilter Field:=3
>>> Selection.AutoFilter Field:=4 etc.
>>> -------------------------------------------------------------------------
>>> Have tried -
>>>
>>> Sub Macro2()
>>> '
>>> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4, Field:=5, Field:=6
>>> End Sub
>>> --------------------------------------------------------------------------
>>> Sub autofilter_off()
>>> With ActiveSheet
>>> .EnableAutoFilter = True
>>> End With
>>> End Sub
>>> -------------------------------------------------------------------------
>>>
>>>
>>
>>
>
>

MBlake · 08-25-2005, 03:05 PM

Sorry Ron,
I should have spotted the typo myself, I am very grateful for your help.

Mickey

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Btw : Sorry for the typo (ActiveSheeet instead of ActiveSheet)
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "MBlake" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Ron,
>> I am getting an error of 'Object Required' when I use it as either the
>> below - I normally use Option Explicit but if that is included I also get
>> an Variable Not Defined message.
>>
>> I'd appreciate one more hint if possible.
>>
>> Thanks,
>> Mickey
>>
>>
>> Sub autofilter_off()
>> With ActiveSheet
>> ActiveSheeet.AutoFilterMode = False
>> End With
>> End Sub
>> -----------------------------------------------
>> Sub autofilter_off()
>> ActiveSheeet.AutoFilterMode = False
>> End Sub
>> -----------------------------------------------
>>
>>
>> "Ron de Bruin" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi Mickey
>>>
>>> ActiveSheeet.AutoFilterMode = False
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>> "MBlake" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hi,
>>>> I need to turn off the auto filter option and ensure any filtered
>>>> columns are returned to non-filter state prior to running some VBA, at
>>>> present I am using Selection.AutoFilter Field:=1 and repeating it for
>>>> each of the umpteen columns. This looks rather messy and adds a lot of
>>>> lines to code. I am sure it can be scaled down to one line, however so
>>>> far I haven't managed it. I have tried a few ideas to no avail -
>>>>
>>>> I'd appreciate any advice on this one,
>>>> Best Wishes,
>>>> Mickey
>>>>
>>>> -------------------------------------------------------------------------
>>>> Currently Using -
>>>> Selection.AutoFilter Field:=1
>>>> Selection.AutoFilter Field:=2
>>>> Selection.AutoFilter Field:=3
>>>> Selection.AutoFilter Field:=4 etc.
>>>> -------------------------------------------------------------------------
>>>> Have tried -
>>>>
>>>> Sub Macro2()
>>>> '
>>>> Selection.AutoFilter Field:=1, Field:=2, Field:=3, Field:=4,
>>>> Field:=5, Field:=6
>>>> End Sub
>>>> --------------------------------------------------------------------------
>>>> Sub autofilter_off()
>>>> With ActiveSheet
>>>> .EnableAutoFilter = True
>>>> End With
>>>> End Sub
>>>> -------------------------------------------------------------------------
>>>>
>>>>
>>>
>>>
>>
>>
>
>