Hide/unhide rows based on "If" statements

[jjt1973]

I am trying to clean up my template for multiple users. I am looking for a solution to help me hide/unhide rows based on "IF" statements. My example:

.........Column A......................................Column E
1.......transaction type:...........................(purchase, lease)
2.......=If(E1="Lease","Lease Term","")
3.......How many customers:.....................(1, 2, 3, 4)
4.......Customer 1
5.......=If(E3>1,"customer 2","")
6.......=If(E3>2,"Customer 3","")
7.......=if(E3>3,"Customer 4","")
8........Downpayment:
9......."If" statement
10......"if" statement

So, basically I need a solution where when the "if" statement is false the corresponding row hides and when the "IF" statement is true it unhides. Is this possible? I also need it to refresh evertime the worksheet is open (think template).

Thanks, JT

 

[WinteE]

Create the if statements in a way that when the row should be hidden the result is 1 and 0 when it should be shown. Add an autofilter to the column where the =IF()'s are in.
Now you can show all rows where the result is 0.

Success

 

[jjt1973]

I appreciate the solution.. one problem.. i have never used autofilters before. How would I convert my IF statement so that the results are 1 and 0 without losing the actual "if" statement dialogue? The example I gave is a stripped down simplified version of the actual.

JT

 

[WinteE]

In the IF statements that you use, a "Blank" value is returned when False.
You can use a filter (Select the range to filter -> Data, Filter, Autofilter) on "Blanks".

 

[jjt1973]

I can see how that would work.. however, when I get to the autofilter I tried inputing "Not Equal to": "Blanks".. Nothing happened. What did i do wrong?

 

[WinteE]

Try Not equal to ""

 

[jjt1973]

Using Not equal to "" did not work, However, I made the value IF FALSE to equal zero instead of "" and then ran it as "If not equal to" "0" and that worked. Thanks.

Now the issue is how do I get it to automatically refresh if the data filtered changes? This form will be a template and used by our national sales team so having them refresh the filter each time isn't realistic. JT

 

[WinteE]

Hi,

To activate the filter on changes that are made to a worksheet the macro you need would be like below.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    'Range("A1:A100") is the range where changes will activate the filter
        ActiveSheet.Range("$A$2:$A$9").AutoFilter Field:=1, Criteria1:="="
    End If
    
End Sub

The Intersect function restricts the range where changes will activate the code between If and End If.

The Field value for the Autofilter is 1 here, but may be another number. You best check this out with the macro recorder.

Success,
Erik

 

[jjt1973]

It all worked, thanks. I really appreciate the help.