Disabling and re-enabling the AutoFilter from VBA
 

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

Disabling and re-enabling the AutoFilter from VBA
 

try this

ActiveSheet.AutoFilterMode = False

--


Gary


"CFD" wrote in message
...

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

Disabling and re-enabling the AutoFilter from VBA
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=476079

Disabling and re-enabling the AutoFilter from VBA
 

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

Disabling and re-enabling the AutoFilter from VBA
 

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

Disabling and re-enabling the AutoFilter from VBA
 

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

Disabling and re-enabling the AutoFilter from VBA
 

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

--


Gary


"CFD" wrote in message
...

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

Disabling and re-enabling the AutoFilter from VBA
 

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?


--
CFD


------------------------------------------------------------------------
CFD's Profile: http://www.excelforum.com/member.php...o&userid=27306
View this thread: http://www.excelforum.com/showthread...hreadid=476079