Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    177
    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 great many people will think they are thinking when they are merely rearranging their prejudices." - William James

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 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
    177
    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 great many people will think they are thinking when they are merely rearranging their prejudices." - William James

Posting Permissions

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