Results 1 to 7 of 7

Thread: Excel 2010 Dynamic Autofilter on Change

  1. #1

    Excel 2010 Dynamic Autofilter on Change



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good morning,

    I have a workbook with various sheets containing extractions, user inputs, and various formulas. In Sheet2, I have a report that is generated based off of the information within some of these other sheets. The length of this report is currently maintained manually with a filter. The filter field is a helper that uses a formula to decide if the row is valid or not (1 or 0). What I would like, is to have a little bit of background VBA code that will envoke the filter whenever the values in this filter field change. The filter criteria is always 1, the row header is always T12 and the filter range is always T12:T141 (all within Sheet2). It doesn't seem like this would be that hard... but I can't make it work. Thoughts?

    Thanks,

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    try:
    Code:
    Private Sub Worksheet_Calculate()
    AutoFilter.ApplyFilter
    End Sub
    in sheet2's code-module.

  3. #3
    Thank you for the suggestion, but this didn't work as needed.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by bgoree09 View Post
    Thank you for the suggestion, but this didn't work as needed.
    Well it did here when I tested it.

    You're not giving much information when you state 'didn't work'.
    The helper column contains a formula? Right?
    Calculation is set to Automatic isn't it?
    Macros are enabled?
    The code is in Sheet2's code module?
    Application.EnableEvents hasn't been set to false?

    Otherwise we could use a sheetactivate event if users change something on another sheet and only switches to Sheet2 to have a look.
    Or a sheetchange event on one or all of the other sheets to execute a Sheets("Sheet2").autofilter.applyfilter line.

  5. #5
    Hello again,

    Sorry for the lack of information. It turns out that I did have the code in the wrong place. Originally, the formula did nothing (since I had it in the wrong location). I have moved it to the Sheet2 Module, but it appears to loop infintely (Run-time error... Method 'ApplyFilter' of object 'AutoFilter' failed). The answer to all of your questions is yes, currently. The most reasonable location for changes to the helper column is contained withing Sheet2. It is not inconceiveable that information in Sheet1 could be changed to effect this column, but I'm much less concerned with it, since it would involve a deviation from standard practice.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    try:
    Code:
    Private Sub Worksheet_Calculate()
    On Error GoTo here
    Application.EnableEvents = False
    AutoFilter.ApplyFilter
    here:
    Application.EnableEvents = True
    End Sub
    An autofilter needs to be already on the sheet and filtering something; this code just re-applies the filter (refreshes it, if you will).
    Last edited by p45cal; 2013-08-21 at 12:49 AM.

  7. #7
    Excellent. Thanks for the help.

Posting Permissions

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