| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically Hide Rows
Is there a way to format a worksheet to automatically hide rows if a certain
cell = a certain number? -- Tracy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
Hi,
Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
Hi Mike, I tried that, but it didn't seem to work. The row needs to
disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
Tracy,
Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
O.k I copied and pasted the information and then closed the application. The
MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? -- Tracy "Mike H" wrote: Tracy, Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
If you put the code where i advised then all you need to do is type 0 or 1 in
column D and the row where you put it will hide. Mike "Tracy" wrote: O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? -- Tracy "Mike H" wrote: Tracy, Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
Tracy wrote:
O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? Have you considered using an AutoFilter? Not automatic, but fairly simple to select only rows with a 1 in a certain column. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
O.k I'm really close, but when I put in the number 1 the line disappears. I
need it to remain. You've been so very helpful. Thanks so much. -- Tracy "Mike H" wrote: If you put the code where i advised then all you need to do is type 0 or 1 in column D and the row where you put it will hide. Mike "Tracy" wrote: O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? -- Tracy "Mike H" wrote: Tracy, Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
I guess this doesn't work on a worksheet where I already have all the
information put into column "B"? -- Tracy "Mike H" wrote: If you put the code where i advised then all you need to do is type 0 or 1 in column D and the row where you put it will hide. Mike "Tracy" wrote: O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? -- Tracy "Mike H" wrote: Tracy, Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
ammended to hide the row only if a zero is entered. To hide rows that would
have been hidden previously had you had this code simply re-enter the zero. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("B:B")) Is Nothing Then If IsNumeric(Target) And Target.Value = O Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: I guess this doesn't work on a worksheet where I already have all the information put into column "B"? -- Tracy "Mike H" wrote: If you put the code where i advised then all you need to do is type 0 or 1 in column D and the row where you put it will hide. Mike "Tracy" wrote: O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? -- Tracy "Mike H" wrote: Tracy, Slow down and read the advice I gave you, here it is again. Right click your sheet tab, in the popup click on 'View code' and paste the code in on the right. You still haven't told me which cell a 'certain cell' is so this works on column D. If Not Intersect(Target, Range("D:D")) Is Nothing Then Change the D:D to whatver column you want F:F for column F for example. I changed the code to work with 0 & 1. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value = O _ Or Target.Value = 1 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Hi Mike, I tried that, but it didn't seem to work. The row needs to disappear if there is a 0 in a certain cell. There are only two choices a "0" or a "1". I can change those numbers to 1/2 or 3/4 if that helps. Also when I paste the information does it need to be on Worksheet at the top or anything else I may be missing? -- Tracy "Mike H" wrote: Hi, Not with a format but you can do it with event code like this. Right click your sheet tab, view code and paste the code below in. As soon as a value 10 is entered in column D then the row is hidden. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D:D")) Is Nothing Then If IsNumeric(Target) And Target.Value 10 Then Target.EntireRow.Hidden = True End If End If End Sub Mike "Tracy" wrote: Is there a way to format a worksheet to automatically hide rows if a certain cell = a certain number? -- Tracy |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Automatically Hide Rows
It works if all I want to do is view those rows only, but I have other rows
that need to remain in place. I was hoping for a simple fix. -- Tracy "Glenn" wrote: Tracy wrote: O.k I copied and pasted the information and then closed the application. The MS Visual Basic is still on my task bar. The Column is "B" and I have two choices to input, either 1 or 0. My spreadsheet is over 3000 rows long and I need to view only the rows which contain the 1. Does the code automatically work or do I have to do something else? Have you considered using an AutoFilter? Not automatic, but fairly simple to select only rows with a 1 in a certain column. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically Hide rows with a value of zero | Excel Worksheet Functions | |||
| How do I set up Excel to automatically hide certain rows? | Excel Discussion (Misc queries) | |||
| automatically hide rows with zero value | Excel Worksheet Functions | |||
| Hide rows automatically | Excel Discussion (Misc queries) | |||
| How do I automatically hide rows | Excel Discussion (Misc queries) | |||
Linear Mode
