Re: Active Filtering

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank for your help. it now works quite well!

"Dave Peterson" wrote:

> Instead of this:
> Set rng = Range(Me.Name & "!_FilterDatabase")
> Set rng2 = Range(Me.Name & "!Criteria")
> try:
> Set rng = me.range("_FilterDatabase")
> Set rng2 = me.range("Criteria")
>
> But I think you have to run the filter at least once to make sure those range
> names exist.
>
> Chris wrote:
> >
> > Thankyou for the reply Norman,
> >
> > However I am still having problems.
> >
> > Firstly, I do not understand how the Range object is being used, and
> > secondly, if there is any modification required to the code to 'suit' my
> > speadsheet.
> >
> > I assume I do need some modifcation as I get an error on the line:
> > Set rng = Range(Me.Name & "!_FilterDatabase")
> >
> > What are your suggestions?
> >
> > Chris
> >
> > "Norman Jones" wrote:
> >
> > > Hi Chris,
> > >
> > > Assume that you have an Advanced Filter range and criteria range set.
> > >
> > > If the filter hours are non-formula values, try:
> > >
> > > '=============================>>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim rng As Range, rng2 As Range
> > >
> > > Set rng = Range(Me.Name & "!_FilterDatabase")
> > > Set rng2 = Range(Me.Name & "!Criteria")
> > >
> > > If Not Intersect(Target, rng) Is Nothing Then
> > > rng.AdvancedFilter _
> > > Action:=xlFilterInPlace, _
> > > CriteriaRange:=rng2, _
> > > Unique:=False
> > > End If
> > >
> > > End Sub
> > > '<<=============================
> > >
> > >
> > > If, however, the hours are the result of formulas, try instead:
> > >
> > > '=============================>>
> > > Private Sub Worksheet_Calculate()
> > >
> > > Dim rng As Range, rng2 As Range
> > >
> > > Set rng = Range(Me.Name & "!_FilterDatabase")
> > > Set rng2 = Range(Me.Name & "!Criteria")
> > >
> > > rng.AdvancedFilter _
> > > Action:=xlFilterInPlace, _
> > > CriteriaRange:=rng2, _
> > > Unique:=False
> > >
> > > End Sub
> > > '<<=============================
> > >
> > > These are both worksheet event procedures and should be pasted into the
> > > worksheets's code module (not a standard module and not the workbook's
> > > ThisWorkbook module):
> > >
> > > ************************************************************
> > > Right-click the worksheet's tab
> > >
> > > Select 'View Code' from the menu and paste the code.
> > >
> > > Alt-F11 to return to Excel.
> > > ************************************************************
> > >
> > > If you are not familiar with macros, you may wish to visit David McRitchie's
> > > 'Getting Started With Macros And User Defined Functions' at:
> > >
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > >
> > > "Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:B27C18EA-AC55-4248-B22A-B2DA331D83D6@xxxxxxxxxxxxxxxx
> > > >I have a spreadsheet that contains work-categories, rates and hours for
> > > > estimating time to perform jobs.
> > > > I wish to display only the categories that have >0 hours attributed to
> > > > them
> > > > in a final table, used in documentation for customers.
> > > > I have tried using advanced filtering, but to limited success. If I apply
> > > > the filter, but then later give a couples of hours work to a previously
> > > > non-used catergory, I then have to RE-apply the filter to get that row to
> > > > display.
> > > >
> > > > Can you have an ACTIVE filter (i.e. if the values changes to be greater
> > > > than
> > > > zero - the rows automatically become non-hidden) and if so, how do you do
> > > > this??
> > > >
> > > > Thanks for your help in advance
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>
.

Relevant Pages

  • Re: filtered pasting
    ... Dim RngToCopy As Range ... Dim RngToPaste As Range ... "Dave Peterson" wrote: ... When I took off the filter, the rows that were hiddend did not ...
    (microsoft.public.excel.misc)
  • Re: Problem with AutoFilter
    ... "Dave Peterson" wrote: ... Dim wkst As Worksheet ... 'remove any existing filter. ...
    (microsoft.public.excel.programming)
  • Re: Problem with AutoFilter
    ... "Dave Peterson" wrote: ... Dim wkst As Worksheet ... 'remove any existing filter. ...
    (microsoft.public.excel.programming)
  • Re: AutoFilter
    ... "Dave Peterson" wrote: ... .Weight = xlMedium ... .ColorIndex = xlAutomatic ... I need to just select the filter data only. ...
    (microsoft.public.excel.programming)
  • Re: filtered pasting
    ... "Dave Peterson" wrote: ... and pastes into the visible cells. ... When I took off the filter, the rows that were hiddend did not ...
    (microsoft.public.excel.misc)