Resetting autofilters

[Pete_UK]

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]

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

 

[Pete_UK]

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

Pete

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