+ Reply to Thread
Results 1 to 4 of 4

Automatically Refresh Autofilter (2007)

  1. #1
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Automatically Refresh Autofilter (2007)

    Hi Everyone,

    This topic has been raised in other forums, but not here that I find.

    Moreover, I can't find a definitive solution.

    Here's the situation:

    I have table filled with formulas. I use an Autofilter at the top of one column to filter out data that cannot be charted (zeros, nulls, negatives, etc..).

    The table is highly dynamic. A small change in any input dramatically changes the data across the table.

    Here's the problem:

    My charts rely on the Autofilter to filter out data that cannot be charted. However, I often forget to hit the "reapply filters" button when the table data changes. This results in wildly inaccurate charts.

    What I'd like:

    Some way to ensure that the Autofilter automatically refreshes whenever the data in its column changes. This will probably require VBA --and I have essentially zero VBA skills.

    What I've found:

    I've found some VBA code from the very last post in an Ozgrid thread ( http://tinyurl.com/n288pg ). I tried to cut and paste the code into this post, but it pasted as an indecipherable hunk of condensed text.

    Anyway, the guy who supplied the code is ecstatic that it will refresh an Autofilter whenever the column data changes.

    What I did:

    I accessed the VBA code for my data sheet, and copied the Ozgrid VBA code into something called "ThisWorksheet" (just below the words "Option Explicit"). Then I amended the code to identify the column whose filter I want to automatically refresh (specifically I changed "A3:A50" to "BA5:BA270").

    What happened:

    Nothing. I see no difference in behavior. The Autofilter does not refresh when the data in the BA column changes.

    What now:

    Since I prefer Excel Forum over OzGrid, I'm asking for help here first!

    Ideas, anyone?

    Cheers,

    Jay

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Automatically Refresh Autofilter (2007)

    link is broken have you the full url?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Automatically Refresh Autofilter (2007)

    Hi Martin,

    First, I am SO grateful for your interest and assistance!

    Here is the full URL: http://www.ozgrid.com/forum/showthread.php?t=84539

    The relevant VBA code is in the very last post.

    Cheers!

    Jay

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Automatically Refresh Autofilter (2007)

    now that ive looked im afraid im none the wiser! re ask in the programming forum with a link to this post btw here's that code

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1