Active Filtering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
 
Thank for your help. it now works quite well!

Dave Peterson said:
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.
Click to expand...
 
Back
Top