T
Tracy
Is there a way to format a worksheet to automatically hide rows if a certain
cell = a certain number?
cell = a certain number?
M
Mike H
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
")) Is Nothing Then
If IsNumeric(Target) And Target.Value > 10 Then
Target.EntireRow.Hidden = True
End If
End If
End Sub
Mike
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
")) Is Nothing ThenIf IsNumeric(Target) And Target.Value > 10 Then
Target.EntireRow.Hidden = True
End If
End If
End Sub
Mike
T
Tracy
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?
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?
M
Mike H
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")) Is Nothing Then
Change the 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")) 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
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
")) Is Nothing ThenChange the 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
")) Is Nothing ThenIf IsNumeric(Target) And Target.Value = O _
Or Target.Value = 1 Then
Target.EntireRow.Hidden = True
End If
End If
End Sub
Mike
T
Tracy
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?
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?
M
Mike H
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
column D and the row where you put it will hide.
Mike
G
Glenn
Tracy said:
Have you considered using an AutoFilter? Not automatic, but fairly simple to
select only rows with a 1 in a certain column.
T
Tracy
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.
need it to remain. You've been so very helpful. Thanks so much.
T
Tracy
I guess this doesn't work on a worksheet where I already have all the
information put into column "B"?
information put into column "B"?
M
Mike H
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
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
T
Tracy
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.
that need to remain in place. I was hoping for a simple fix.