Advanced Filter
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Advanced Filter
Hi anyone,
I’m trying to figure out a worksheet event code that would hide the rows that does not meet the two data validation cells on my worksheet.
My workbook does hide the rows by category. How could I make it to hide the rows when it selects the actor?
Any help on this would be kindly appreciated.
I’m trying to figure out a worksheet event code that would hide the rows that does not meet the two data validation cells on my worksheet.
My workbook does hide the rows by category. How could I make it to hide the rows when it selects the actor?
Any help on this would be kindly appreciated.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
See attached workbook. The criteria range is now F2:G3 - take a look at the formulas in F3 and G3.
The code has been changed to
The code has been changed to
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C3,C5"), Target) Is Nothing Then
Application.EnableEvents = False
Range("B8").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("F2:G3"), Unique:=False
Application.EnableEvents = True
End If
End Sub
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Thanks for the help Hans. I do really appreciate it.The code and the sheet looks better now with the new ranges.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
First of all I would like to apologize for posting miscellaneous codes on this post. I hope I'm within the limits.
My intention is to put up all the codes in the worksheet "Select Movies" of the attached workbook so that the sheet does what is mentioned in the code.
The following code sets the print area starting from row 3 as data gets added to the rows of the worksheet.
The following code puts up date & time in column “C†as data is entered to the column D.
The following code does hide the rows that do not meet the data validation.
The following code calculates the age in reference to the year in column 7 and puts the value in column 8.
How could I combine the four codes so that the final code adds date & time to the column C as data is entered to column D.? And Sets the print area as the sheet’s rows get’s filled with data. And the sheet gets filtered with the values in the data validation cell.
Any help on this would be kindly appreciated.
My intention is to put up all the codes in the worksheet "Select Movies" of the attached workbook so that the sheet does what is mentioned in the code.
The following code sets the print area starting from row 3 as data gets added to the rows of the worksheet.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim myrange As String
myrange = Cells(Rows.Count, 10).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$C$3:" & myrange
Application.ScreenUpdating = True
End Sub
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Static runme As Boolean
If (runme = False And Target.Column = 4) Then
runme = True
Cells(Target.Row, Target.Column - 1).Value = Now()
Else: runme = False
End If
End Sub
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E2,G2"), Target) Is Nothing Then
Application.EnableEvents = False
Range("C9").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("L1:M2"), Unique:=False
Application.EnableEvents = True
End If
End Sub
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 6 Then
Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" months"",DATEDIF(RC[-1],NOW(),""y"")))"
End If
Application.ScreenUpdating = True
End Sub
Any help on this would be kindly appreciated.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
You can simply combine the four pieces of code into one procedure. Of course, you should have only one line
Private Sub Worksheet_Change(ByVal Target As Range)
at the start, and only one line
End Sub
at the end. And I'd put only one line
Application.ScreenUpdating = False
below Sub Worksheet_Change, and only one line
Application.ScreenUpdating = True
above End Sub.
Private Sub Worksheet_Change(ByVal Target As Range)
at the start, and only one line
End Sub
at the end. And I'd put only one line
Application.ScreenUpdating = False
below Sub Worksheet_Change, and only one line
Application.ScreenUpdating = True
above End Sub.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Thanks for the reply Hans. Here’s how I’ve modified the code.
This modification does insert print area as rows get filled by data. And also it filters with data validation box. Moreover it inserts date & time to the column C of the worksheet.
But as rows get filled with data the code does not calculate the age in column 8.
But it calculates the age if the date is separately written in column 7 without data in any other remaining columns.
What may be the reason for this?
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Range("E2,G2"), Target) Is Nothing Then
Application.EnableEvents = False
Range("C9").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("L1:M2"), Unique:=False
Application.EnableEvents = True
End If
Dim myrange As String
myrange = Cells(Rows.Count, 10).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$C$3:" & myrange
Application.EnableEvents = True
Static runme As Boolean
If (runme = False And Target.Column = 4) Then
runme = True
Cells(Target.Row, Target.Column - 1).Value = Now()
Else: runme = False
End If
If Target.Column = 7 Then
Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" months"",DATEDIF(RC[-1],NOW(),""y"")))"
End If
Application.ScreenUpdating = True
End Sub
But as rows get filled with data the code does not calculate the age in column 8.
But it calculates the age if the date is separately written in column 7 without data in any other remaining columns.
What may be the reason for this?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
Thaty's the way you have written the code: it only calculates the age if the user edits a cell in column G (Target.Column = 7). What exactly did you expect?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
I did excpect the age to get calulated even if a complete data row is copied and pasted to a new rows having only the age column blank.
The combination of the code seems to work pretty slower even with the lines screen updating. Is this due to the wrong combination of the codes?
Moreover, the current version of the code which calculates and enters age to the column “Hâ€, enters the age as 110 years if any text (except date) is written in any row within the column “Gâ€.
In order to prevent this, how could the code be changed so that it calculates the age beginning from row 10 onwards?
The combination of the code seems to work pretty slower even with the lines screen updating. Is this due to the wrong combination of the codes?
Moreover, the current version of the code which calculates and enters age to the column “Hâ€, enters the age as 110 years if any text (except date) is written in any row within the column “Gâ€.
In order to prevent this, how could the code be changed so that it calculates the age beginning from row 10 onwards?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
You now check
If Target.Column = 7 Then
You could add a check on Target.Row.
If Target.Column = 7 Then
You could add a check on Target.Row.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Thanks for the help Hans. The (whole) code seems to be taking a bit long to function. How might I arrange the lines to make it work better.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
The code to set the print area is slow. I'd remove the two lines used to set the print area.
Instead, make the print area dynamic:
- Activate the Formulas tab of the ribbon.
- Click Name Manager.
- Select Print_Area in the list of names.
- Change the Refers To formula to
=OFFSET('Select Movies'!$C$9,0,0,COUNTA('Select Movies'!$J:$J),8)
- Confirm by clicking the check mark to the left of the Refers To box.
- Click Close.
Instead, make the print area dynamic:
- Activate the Formulas tab of the ribbon.
- Click Name Manager.
- Select Print_Area in the list of names.
- Change the Refers To formula to
=OFFSET('Select Movies'!$C$9,0,0,COUNTA('Select Movies'!$J:$J),8)
- Confirm by clicking the check mark to the left of the Refers To box.
- Click Close.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Hans, did you meant to remove these three lines?
I'm asking this because you said to remove two lines even though the code has three lines
Code: Select all
Dim myrange As String
myrange = Cells(Rows.Count, 10).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$C$3:" & myrange
Application.EnableEvents = True
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
You can also remove the declaration (Dim ...), although it wouldn't do any harm if you left it.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Thanks for the reply Hans.
How could I make the code
To start calculating age from row 10.
Here's my final code
How could I make the code
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" months"",DATEDIF(RC[-1],NOW(),""y"")))"
End If
End Sub
Here's my final code
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Range("E2,G2"), Target) Is Nothing Then
Application.EnableEvents = False
Range("C9").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("L1:M2"), Unique:=False
Application.EnableEvents = True
End If
Static runme As Boolean
If runme = False And Target.Column = 4 And Target.Row >= 11 Then
runme = True
Target.Offset(, -1).Value = Now()
Else: runme = False
End If
If Target.Column = 7 Then
Target.Offset(, 1).Formula = "=IF(DATEDIF(RC[-1],NOW(),""y"")>0,DATEDIF(RC[-1],NOW(),""y"") & "" years"",IF(DATEDIF(RC[-1],NOW(),""m"")>0,DATEDIF(RC[-1],NOW(),""ym"") & "" months"",DATEDIF(RC[-1],NOW(),""y"")))"
End If
Application.ScreenUpdating = True
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
Look at your own code, you already have an example of how to take Target.Row into account.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
I'm having the company name and other information starting from row 5.
For this reason to include them in the print area I have changed the formula as
=OFFSET('Select Movies'!$C$5,0,0,COUNTA('Select Movies'!$J:$J),8)
So that the company name and rest would be included when the sheet gets printed. But after changing the formula the print area gets selected from row 5 to 7 where I have data rows. Row 8 is empty and column headers start from row 9.
Does this mean the print area does not select any empty rows? I'm having some empty rows after the company name. The column headers start after the empty rows.
My question of concern is that how could I include the empty rows and column headers and increase the print area as rows of information get added to the worksheet?
For this reason to include them in the print area I have changed the formula as
=OFFSET('Select Movies'!$C$5,0,0,COUNTA('Select Movies'!$J:$J),8)
So that the company name and rest would be included when the sheet gets printed. But after changing the formula the print area gets selected from row 5 to 7 where I have data rows. Row 8 is empty and column headers start from row 9.
Does this mean the print area does not select any empty rows? I'm having some empty rows after the company name. The column headers start after the empty rows.
My question of concern is that how could I include the empty rows and column headers and increase the print area as rows of information get added to the worksheet?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
The number of rows in the print area is now COUNTA('Select Movies'!$J:$J), that is the number of non-blank cells in column J. If you want to include a fixed number of extra rows, you add them, e.g. COUNTA('Select Movies'!$J:$J)+4 to include 4 extra rows.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Advanced Filter
Hans, did you mean the formula should be as follows
=OFFSET('Select Movies'!$C$9,0,0, COUNTA('Select Movies'!$J:$J)+4 If I want to include a fixed number of extra rows in the print area.
As this modification is giving me the message
The formula is missing parenthesis--) or and the add parenthesis in the appropriate place.
=OFFSET('Select Movies'!$C$9,0,0, COUNTA('Select Movies'!$J:$J)+4 If I want to include a fixed number of extra rows in the print area.
As this modification is giving me the message
The formula is missing parenthesis--) or and the add parenthesis in the appropriate place.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Advanced Filter
Your formula is incomplete. Please compare it to the complete formula in your previous reply.
Best wishes,
Hans
Hans