Hide when cell value (x) is < 1 but > -1

[Guest]

Here is the scenario;
A B C D
A 50 25
B 10 9
C 0.5 6
D 0.26 -0.36
E -5 0.99

I would like to be able to hide Row D because the values in column A and C
are < 1 and > -1. Please note that row C and E should not be hidden because
in one of the columns the value is > 1 or < -1.

I would really appreciate any help thanks

Craig

 

[Guest]

I would like to be able to hide Row D because the values in column A and C
are < 1 and > -1.
Applying the above test Row C and E also meet this test. Best to redefine
what you are wanting to get. I'm sure we can help you.

 

[Mike Fogleman]

Jim, I believe the op wants to apply both criteria to each number.
=IF(ABS(SUM(A11))>2,"visible","hidden")

Mike F

 

[Guest]

I entered his table data and in cell F2 (creating a helper column = to TRUE
or FALSE - that He could filter-on) I entered:

=AND(NOT(ISBLANK(B2)),NOT(ISBLANK(D2)),B2<1,D2>-1)

and Got True in F4, F5 and F6.

Oh well,,,

 

[Bernard Liengme]

Suppose there were two values 5 and -5.5. Your formula would result in
"hide".
This might work (needs more testing and problem need better definition)
=IF(SUMPRODUCT(--(ABS(A11)<1))<2,"visible","hidden")
best wishes

 

[Ron Rosenfeld]

Here is the scenario;
A B C D
A 50 25
B 10 9
C 0.5 6
D 0.26 -0.36
E -5 0.99

I would like to be able to hide Row D because the values in column A and C
are < 1 and > -1. Please note that row C and E should not be hidden because
in one of the columns the value is > 1 or < -1.

I would really appreciate any help thanks

Craig

Do you want the row Hidden (e.g. zero height), or do you want the data
invisible?

If the latter, you can do it with conditional formatting.

Select A1
Format/Conditional Formatting
Formula Is: =AND($A1>-1,$A1<1,$C1>-1,$C1<1)
Format/Font and set the Font Color to the same as your background color.

If you actually want to Hide the rows, then you will need to use a VBA event
macro:

Right click on the sheet tab and select View Code.

Enter the code below into the window that opens:

==========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim rw As Range
Set AOI = Range("A1:C5")

For Each rw In AOI.Rows
If rw.Columns(1) < 1 And _
rw.Columns(1) > -1 And _
rw.Columns(3) < 1 And _
rw.Columns(3) > -1 Then
rw.EntireRow.Hidden = True
Else
rw.EntireRow.Hidden = False
End If
Next rw
End Sub
=================================

Note that, the way it displayed on my computer, Row A (or Row 1) would also be
hidden as the entries appear to be in columns B and D, leaving columns A and C
blank.

If you also want to evaluate that there be an actual entry in the cell, and
that non-numeric entries do not get evaluated, then change the code to:

==================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim rw As Range
Set aoi = Range("A1:C5")

For Each rw In aoi.Rows
If rw.Columns(1) > -1 And _
rw.Columns(1) < 1 And _
IsNumeric(rw.Columns(1)) = True And _
Len(rw.Columns(1).Text) > 0 And _
rw.Columns(3) > -1 And _
rw.Columns(3) < 1 And _
IsNumeric(rw.Columns(3)) = True And _
Len(rw.Columns(3).Text) > 0 Then
rw.EntireRow.Hidden = True
Else
rw.EntireRow.Hidden = False
End If
Next rw
End Sub
========================================
--ron

 

[Guest]

That is such a massive help thank you,

I just have a couple of queries

1) If i wanted to apply the same rule to columns B and D should I just copy
the IF formula and change to column numbers?

2) The macro doesn't run when i press run it is only activated once i press
enter/return key after inputting one of the values in the table, is there any
way around this?

Many thanks once again

 

[Ron Rosenfeld]

That is such a massive help thank you,

I just have a couple of queries

1) If i wanted to apply the same rule to columns B and D should I just copy
the IF formula and change to column numbers?

Yes.

Note that the column numbers are related to the AOI range. "1" is always the
first column in the AOI range which, if AOI does not start in Column A, may be
a different column.

2) The macro doesn't run when i press run it is only activated once i press
enter/return key after inputting one of the values in the table, is there any
way around this?

The macro was written as a worksheet change event, so only triggers on a
worksheet change.

If you want to run it optionally, as in Run Macro, then, instead of entering it
as a worksheet event, put it into a regular module (and DELETE it from the
worksheet module).

To enter it into a regular module, when you are in the "View Code" area, from
the top menu bar of the VBEditor select Insert/Module and paste the code below
into the window that opens.

Again, be sure to delete it from the worksheet module.

Modify the name and enter as follows:

==========================================================
Option Explicit
Sub Hide()
Dim aoi As Range
Dim rw As Range
Set aoi = Range("a1:c5")

For Each rw In aoi.Rows
If rw.Columns(1) > -1 And _
rw.Columns(1) < 1 And _
IsNumeric(rw.Columns(1)) = True And _
Len(rw.Columns(1).Text) > 0 And _
rw.Columns(3) > -1 And _
rw.Columns(3) < 1 And _
IsNumeric(rw.Columns(3)) = True And _
Len(rw.Columns(3).Text) > 0 Then
rw.EntireRow.Hidden = True
Else
rw.EntireRow.Hidden = False
End If
Next rw
End Sub
==================================

Many thanks once again

You're welcome. Thanks for the feedback.
--ron