Disabling and re-enabling the AutoFilter from VBA

[CFD]

Hi all, I have some code which pastes data into a worksheet at the last row of entered data. Unfortunately if the autofilter is enabled on the worksheet, the last row it finds will be that last row displayed by the auto filter.

I want to be able to turn the autofilter off temporarily, find the last cell and paste the data, and then re-enable the autofilter with exactly the same criteria it had before.

The Autofilter is not always enabled, so I can't just toggle it, and I cannot for the life of me find the correct code to do it!

Any help would be greatly appriciated

[Leith Ross]

Hello CFD,

Each Worksheetsheet has an AutoFilter property. This is a read/write (toggle) property. Try this...

Worksheets("Sheet1").EnableAutoFilter = False (Turn it off)
Worksheets("Sheet1").EnableAutoFilter = True (Turn it on)

Substitute the sheet you are working with for Sheet1 in the examples.

Sincerely,
Leith Ross

[CFD]

Thanks Leith, that was the first thing I tried, but it does not seem to make any differnce at all- so I figured I must have been doing something wrong.

here is some code associated with a button on the form, which I used to test the code

Please Login or Register  to view this content.

If I run the code (i.e. click the button) nothing changes. I've checked that the code is being executed and it definitely is, but it is doing nothing?

Any idea why?

[Gary Keramidas]

try this

ActiveSheet.AutoFilterMode = False

--


Gary


"CFD" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all, I have some code which pastes data into a worksheet at the last
> row of entered data. Unfortunately if the autofilter is enabled on the
> worksheet, the last row it finds will be that last row displayed by the
> auto filter.
>
> I want to be able to turn the autofilter off temporarily, find the last
> cell and paste the data, and then re-enable the autofilter with exactly
> the same criteria it had before.
>
> The Autofilter is not always enabled, so I can't just toggle it, and I
> cannot for the life of me find the correct code to do it!
>
> Any help would be greatly appriciated
>
>
> --
> CFD
>
>
> ------------------------------------------------------------------------
> CFD's Profile:
> http://www.excelforum.com/member.php...o&userid=27306
> View this thread: http://www.excelforum.com/showthread...hreadid=476079
>

[CFD]

Thanks! That works to turn it off, but it does not work to turn it back on again (i.e. if I use
ActiveSheet.AutoFilterMode = False

.... paste stuff ....

ActiveSheet.AutoFilterMode = True

it comes up with the error "Unable to set the AutofilterMode property of the worksheet class" on the = true line ...?

[cush]

Perhaps .EnableAutoFilter does just what it says:
It "enables the user to auto filter but it does not
determine whether the current state of the data
is filtered or not.

I have used this effectively to accomplish what you
want (I think)

If ActiveSheet.AutoFilterMode then
AcitveSheet.AutoFilter
End if


"CFD" wrote:

>
> Thanks Leith, that was the first thing I tried, but it does not seem to
> make any differnce at all- so I figured I must have been doing
> something wrong.
>
> here is some code associated with a button on the form, which I used to
> test the code
>
> Code:
> --------------------
>
> Sub filt_off()
> Worksheets("DATABASE").Unprotect (pwd)
> Worksheets("DATABASE").EnableAutoFilter = False
> Worksheets("DATABASE").Protect (pwd)
> End Sub
>
> --------------------
>
>
> If I run the code (i.e. click the button) nothing changes. I've checked
> that the code is being executed and it definitely is, but it is doing
> nothing?
>
> Any idea why?
>
>
> --
> CFD
>
>
> ------------------------------------------------------------------------
> CFD's Profile: http://www.excelforum.com/member.php...o&userid=27306
> View this thread: http://www.excelforum.com/showthread...hreadid=476079
>
>

[Gary Keramidas]

if you have code that sets it up, just rerun it. if you use the lastrow
variable, it should work fine

--


Gary


"CFD" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks! That works to turn it off, but it does not work to turn it back
> on again (i.e. if I use
> ActiveSheet.AutoFilterMode = False
>
> ... paste stuff ....
>
> ActiveSheet.AutoFilterMode = True
>
> it comes up with the error "Unable to set the AutofilterMode property
> of the worksheet class" on the = true line ...?
>
>
> --
> CFD
>
>
> ------------------------------------------------------------------------
> CFD's Profile:
> http://www.excelforum.com/member.php...o&userid=27306
> View this thread: http://www.excelforum.com/showthread...hreadid=476079
>

[CFD]

Thank you for all the help guys ... the autofilter functions are not particullarly straight forward to understand, but I did find an excellent resource on their use ...

http://www.contextures.com/xlautofilter03.html

Runs throught pretty much everything you want to use them for. I've fixed the problem using this resources as a guide. The VB Help files actually say that you cannot set AutoFilterMode to True, only False?

[Ricky_Urban]

I know this individual was able to figure out his problem, but didn't state exactly how. I wanted to add to this, because I was facing the same issue. I had a working filtration, but then I added code to remove the filter and it would not let me add the filter back. I figured out that my filter range was what was stopping it from working. I originally had the following:

ActiveSheet.Range("J1:J50000").Autofilter 10, "PC"

That worked before I introduced:

ActiveSheet.AutoFilterMode = False

It wouldn't work again after that. I tried all of the suggestions that I could find, but eventually what worked is when I reworked my original code to:

ActiveSheet.Range("J1").Autofilter 10, "PC"

I'm not sure what difference it made, but ultimately that is what solved my dilemma.