Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enabling/disabling a set of code from within a spreadsheet sheet | Excel Discussion (Misc queries) | |||
Help with Enabling / Disabling Menu Items..! | Excel Programming | |||
Disabling/enabling events with a button created by code?? | Excel Programming | |||
Disabling/Enabling Macros | Excel Programming | |||
Enabling/Disabling Macros | Excel Programming |