Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Dynamic Autofiltering?? (2000 SP3)

    Is it possible to set up a simple dynamic filter, either by using the AutoFilter functioning or @#$!% it in VBA? I manage a report that's used to monitor the progress of various installations. The process is complete when a date appears in the field marked "Completed" - if the cell is blank, the installation is still in progress. I have a conditional format set such that, if the "Completed" cell isn't blank, the whole row is formatted with a coloured background.

    Records for new installations are added as new rows at the bottom of the workbook and gradually, the earlier installatios drift off the top of the visible pane. I need to maintain vsibility of older uncompleted installations, but I also want users to know that particular installations are complete. I thought of filtering the sheet to show only those records where the "Completed" cell is blank or if it's less that (say) 14 days old. That gives everyone a chance to notice the completed installations, but means the report won't become bogged down with completions such that earlier ongoing installations are missed or otherwise forgotten. I can save this as a view to enable toggling between the filtered list and the completed list if we need to see everything, (for trend analysis etc.).

    I though of using Autofilter but can't make it deal with calculations. "Is Less Than" works well for a finite date, but I can't get it to work for "Now()-14" , for example. I recoded a couple of macros to see how the autofilter was set in VBA and tried playing with the criteria statements but can't seem to get anything to work - so far, my attempts either don't filter anything or everything disappears.

    Is my approach too simplistic? Am I missing something very obvious, (a common problem!)? Is advanced Filter the only way forward? I don't want to rely on a third-party add-in because I want the other users of the report to be able to use it with the minimum of fuss. I'd rather write a couple of macros and save them with the workbook or implement the filter manually and save the views I mentioned earlier.

    Can any one suggest a solution?
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Autofiltering?? (2000 SP3)

    See the attached workbook for an example. It uses Advanced Filter.

    There is a Worksheet_Change event procedure that automatically adjusts the filter if cell C2 is changed:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Range("A4").CurrentRegion.AdvancedFilt er _
    Action:=xlFilterInPlace, _
    CriteriaRange:=ActiveSheet.Range("C1:C2")
    Application.EnableEvents = True
    End If
    End Sub

    There are four command buttons (from the Forms toolbar) on the sheet that set a filter by populating cell C2, for example the button with caption "Recent" executes this macro:

    Sub FilterRecent()
    ActiveSheet.Range("C2") = ">=" & (Date - 14)
    End Sub

    The user can also change C2 manually.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Dynamic Autofiltering?? (2000 SP3)

    Thanks, Hans. You've come up with a nice solution. Meantime, however, I discovered it was my syntax that was preventing the AutoFilter option from working correctly. Notwithstanding that I can't use it in the AutoFilter dialog box, the filtering works if I use the following VBA:

    Criteria1:=">" & Format$((Now() - 14), "dd-mmm-yy")

    Previously, I was trying to wrap the whole criteria phrase in quotes and the VBA editor objected to the quotes before the "dd-..." of the format string, bur if I left out the format string, then nothing was shown in the workbook.

    Many thanks again for your response.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

Posting Permissions

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