Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
Dear Excel Power Users,
column b has an expression that results in a zero or 1, I wish to filter out those rows in a range in which column b has a value of zero Using forms control, I created a forms chk box with the intent to be able to hide those rows with column b = 0, and then expand the rows when box is unchecked. the expression in column b looks at column A which has a date and assigns a value of 1 for those dates within 5 days of today's date. Expression in column b=IF($A108<INT(NOW())-15,0,IF($A108INT(NOW())+15,0,1)) well i could not get it to work that way, can u advise on how to get one form chk box to filter out/hide those rows and expand those same rows when unchecked? Thanks but I added the below macros and assigned each to one form chk box. So that now I chk the box assigned to hiderow_criteria and it does what I want, but then I have to chk the other box to get to expand. Sub hiderow_criteria() ' Range("A16:B16").Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="1" End Sub ' Sub Unhiderow_criteria() ' ' Unhiderow_criteria Macro ' Macro recorded 1/4/2005 by dabbad ' Selection.AutoFilter End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
You can check the value of that checkbox:
Option Explicit Sub ShowHide() Dim CBX As CheckBox With ActiveSheet Set CBX = .CheckBoxes(Application.Caller) If CBX.Value = xlOn Then .Range("a16:b16").AutoFilter Field:=2, Criteria1:="1" Else If .AutoFilterMode Then .ShowAllData End If End If End With End Sub deanop wrote: Dear Excel Power Users, column b has an expression that results in a zero or 1, I wish to filter out those rows in a range in which column b has a value of zero Using forms control, I created a forms chk box with the intent to be able to hide those rows with column b = 0, and then expand the rows when box is unchecked. the expression in column b looks at column A which has a date and assigns a value of 1 for those dates within 5 days of today's date. Expression in column b=IF($A108<INT(NOW())-15,0,IF($A108INT(NOW())+15,0,1)) well i could not get it to work that way, can u advise on how to get one form chk box to filter out/hide those rows and expand those same rows when unchecked? Thanks but I added the below macros and assigned each to one form chk box. So that now I chk the box assigned to hiderow_criteria and it does what I want, but then I have to chk the other box to get to expand. Sub hiderow_criteria() ' Range("A16:B16").Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="1" End Sub ' Sub Unhiderow_criteria() ' ' Unhiderow_criteria Macro ' Macro recorded 1/4/2005 by dabbad ' Selection.AutoFilter End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
Thanks Dave for the feedback......I added the propsed code and assigned
to a new forms control chk box. When the box is checked, the code adds filter drop downs but does not proceed to filter out the rows. When the box is unchecked, the code excecution is interrupted because the .ShowAll Data method fails Any thoughts on how to overcome..... Thx, Deano |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
Option Explicit
Sub ShowHide() Dim rng as Range With Activesheet set rng = .Range(.Range("A16"),.Range("A16").End(xldown)) End With set rng = rng.Resize(,2) Dim CBX As CheckBox With ActiveSheet Set CBX = .CheckBoxes(Application.Caller) If CBX.Value = xlOn Then rng.AutoFilter Field:=2, Criteria1:="1" Else If .AutoFilterMode Then if .FilterMode then .ShowAllData End if End If End If End With End Sub Should fix it. -- Regards, Tom Ogilvy "deanop" wrote in message oups.com... Thanks Dave for the feedback......I added the propsed code and assigned to a new forms control chk box. When the box is checked, the code adds filter drop downs but does not proceed to filter out the rows. When the box is unchecked, the code excecution is interrupted because the .ShowAll Data method fails Any thoughts on how to overcome..... Thx, Deano |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
Thanks for the correction, Tom.
Tom Ogilvy wrote: Option Explicit Sub ShowHide() Dim rng as Range With Activesheet set rng = .Range(.Range("A16"),.Range("A16").End(xldown)) End With set rng = rng.Resize(,2) Dim CBX As CheckBox With ActiveSheet Set CBX = .CheckBoxes(Application.Caller) If CBX.Value = xlOn Then rng.AutoFilter Field:=2, Criteria1:="1" Else If .AutoFilterMode Then if .FilterMode then .ShowAllData End if End If End If End With End Sub Should fix it. -- Regards, Tom Ogilvy "deanop" wrote in message oups.com... Thanks Dave for the feedback......I added the propsed code and assigned to a new forms control chk box. When the box is checked, the code adds filter drop downs but does not proceed to filter out the rows. When the box is unchecked, the code excecution is interrupted because the .ShowAll Data method fails Any thoughts on how to overcome..... Thx, Deano -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
It worked like a champ...thanks...so it needed to know the end of the
autofilter range, but if u look at the code I created I did not have to define an end for autofilter range, why did Dave's code failed to execute.....Thx, deano |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
chk box to filter or hide rows based on value in a column
I figured that letting excel guess worked for you before, it would work for you
in code. (And I bet it still would if you don't have any gaps in that range.) But the real problem was my mistake in this area: If .AutoFilterMode Then .ShowAllData End If Tom corrected it to: If .AutoFilterMode Then if .FilterMode then .ShowAllData End if End If ..autofiltermode checks to see if you have those visible arrows applied. ..filtermode checks to see if you're actually filtering any of the columns. (I had a brain freeze and my little testing wasn't much of a test.) deanop wrote: It worked like a champ...thanks...so it needed to know the end of the autofilter range, but if u look at the code I created I did not have to define an end for autofilter range, why did Dave's code failed to execute.....Thx, deano -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - hide rows that are 0 without an auto-filter | Excel Discussion (Misc queries) | |||
Hide Rows based on value, but not using autofilter | Excel Worksheet Functions | |||
Hide unused rows based on column info | Excel Worksheet Functions | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Hide Rows based on value | Excel Discussion (Misc queries) |