Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
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. "Craig147" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
Jim, I believe the op wants to apply both criteria to each number.
=IF(ABS(SUM(A1:D1))2,"visible","hidden") Mike F "Jim May" wrote in message ... 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. "Craig147" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
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,,, "Mike Fogleman" wrote: Jim, I believe the op wants to apply both criteria to each number. =IF(ABS(SUM(A1:D1))2,"visible","hidden") Mike F "Jim May" wrote in message ... 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. "Craig147" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
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(A1:D1)<1))<2,"visible","hidden") best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mike Fogleman" wrote in message m... Jim, I believe the op wants to apply both criteria to each number. =IF(ABS(SUM(A1:D1))2,"visible","hidden") Mike F "Jim May" wrote in message ... 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. "Craig147" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
On Mon, 8 Oct 2007 01:13:01 -0700, Craig147
wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
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" wrote: On Mon, 8 Oct 2007 01:13:01 -0700, Craig147 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide when cell value (x) is < 1 but -1
On Mon, 8 Oct 2007 05:43:02 -0700, Craig147
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change the size of a cell and hide what is in the cell? | Excel Worksheet Functions | |||
auto-hide rows, cell format (# and @), update cell refs, shade cel | Excel Discussion (Misc queries) | |||
Hide cell values based on a condition in another cell | Excel Worksheet Functions | |||
Hide row if cell = 0 | Excel Worksheet Functions | |||
Hide Value of Cell | Excel Discussion (Misc queries) |