Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Active Filtering
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Handle on active shape and slide | Charts and Charting in Excel | |||
Woorksheet locked on active cell | Excel Worksheet Functions | |||
How do I have an active cell highlight automatically | Excel Discussion (Misc queries) | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) | |||
Unable to set the active cell from VBA | New Users to Excel |