Re: Active Filtering

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

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: Can I use form filtered data for a report?
    ... Private Sub Command16_Click ... Debug.Print "No Filter Set" ... "Wight Dave" wrote: ... DoCmd.OpenReport "Report1", acViewPreview,, strWhere ...
    (microsoft.public.access.reports)
  • RE: Set Filter from Startup form to another form
    ... Private Sub Form_Load ... Dim strPrg As String ... Private Function StartProgramAs String ... Now all that is necessary is to set the filter for each form where you want ...
    (microsoft.public.access.formscoding)
  • Re: Need help with using popup for filtering main form
    ... set filters for the main form. ... The popup is reached from a command button on the main form ... Private Sub ButtonChooseFilter_Click ... 'But popup will only be visible when user requests filter change ...
    (comp.databases.ms-access)
  • Re: Run Time Errors
    ... changed the triple quote in the ... Private Sub cmdFilter_Click ... Build up the criteria string form the non-blank search ... apply the string as the form's Filter. ...
    (microsoft.public.access.formscoding)
  • Re: Search Filter for Report
    ... Private Sub cmdCreateReport_Click ... Allen Browne - Microsoft MVP. ... Limiting a Report to a Date Range ... That example applies the filter string to a form, ...
    (microsoft.public.access.formscoding)