Resetting autofilters
 

I have a macro which copies formulae in row 4 down each column on
Sheet1. There is a header row in row 3 (columns A to K) and
autofilters have been set up in this row. If the User applies a filter
before running the macro (perhaps on data from an earlier run), the
formulae will not be copied down to every cell.

Is there an easy way to un-set any filters at the beginning of my
macro?

As a follow up, though this is not crucial, is it possible to read the
filter status of each column, un-set it if set, copy the formulae down
and then re-apply the same filter(s) at the end of the macro?

Thanks for any advice.

Pete

Resetting autofilters
 

If you want to remove the filter (and filter arrows):

With worksheets("Somesheetnamehere")
.autofiltermode = false
end with

If you want to just show the data, but keep the arrows:

With worksheets("Somesheetnamehere")
if .filtermode then
'some filter is applied
.showalldata
end if
end with

On 09/17/2010 19:38, Pete_UK wrote:
I have a macro which copies formulae in row 4 down each column on
Sheet1. There is a header row in row 3 (columns A to K) and
autofilters have been set up in this row. If the User applies a filter
before running the macro (perhaps on data from an earlier run), the
formulae will not be copied down to every cell.

Is there an easy way to un-set any filters at the beginning of my
macro?

As a follow up, though this is not crucial, is it possible to read the
filter status of each column, un-set it if set, copy the formulae down
and then re-apply the same filter(s) at the end of the macro?

Thanks for any advice.

Pete

--
Dave Peterson

Resetting autofilters
 

Thanks, Dave. I'll give it a try later on.

Pete

On Sep 18, 11:13*am, Dave Peterson wrote:
If you want to remove the filter (and filter arrows):

* * *With worksheets("Somesheetnamehere")
* * * * *.autofiltermode = false
* * *end with

If you want to just show the data, but keep the arrows:

* * *With worksheets("Somesheetnamehere")
* * * * *if .filtermode then
* * * * * *'some filter is applied
* * * * * *.showalldata
* * * * *end if
* * * end with

On 09/17/2010 19:38, Pete_UK wrote:





I have a macro which copies formulae in row 4 down each column on
Sheet1. There is a header row in row 3 (columns A to K) and
autofilters have been set up in this row. If the User applies a filter
before running the macro (perhaps on data from an earlier run), the
formulae will not be copied down to every cell.

Is there an easy way to un-set any filters at the beginning of my
macro?

As a follow up, though this is not crucial, is it possible to read the
filter status of each column, un-set it if set, copy the formulae down
and then re-apply the same filter(s) at the end of the macro?

Thanks for any advice.

Pete

--
Dave Peterson- Hide quoted text -

- Show quoted text -