Advanced Filter

[adam]

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.

[HansV]

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

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

Advanced Filter.xlsm

[adam]

Thanks for the help Hans. I do really appreciate it.The code and the sheet looks better now with the new ranges.

[adam]

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.

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

The following code puts up date & time in column “C” as data is entered to the column D.

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

The following code does hide the rows that do not meet the data validation.

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

The following code calculates the age in reference to the year in column 7 and puts the value in column 8.

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

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.

[HansV]

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.

[adam]

Thanks for the reply Hans. Here’s how I’ve modified the 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
            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

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?

[HansV]

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?

[adam]

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?

[HansV]

You now check

If Target.Column = 7 Then

You could add a check on Target.Row.

[adam]

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.

[HansV]

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.

[adam]

Hans, did you meant to remove these 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

I'm asking this because you said to remove two lines even though the code has three lines

[HansV]

You can also remove the declaration (Dim ...), although it wouldn't do any harm if you left it.

[adam]

Thanks for the reply Hans.
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

To start calculating age from row 10.

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

[HansV]

Look at your own code, you already have an example of how to take Target.Row into account.

[adam]

Thanks Hans. I've figured that out.

[adam]

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?

[HansV]

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.

[adam]

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.

[HansV]

Your formula is incomplete. Please compare it to the complete formula in your previous reply.