Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
Hello All
Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
Hi
Try this: Sub aaa() Dim targetRange As Range Dim tRange As Range Application.ScreenUpdating = False Set targetRange = Range("A1:J100") For Each r In targetRange.Rows Set tRange = targetRange.Rows(r.Row) For Each cell In tRange.Cells If cell.Value = 0 Then Rows(cell.Row).Hidden = True Exit For End If Next Next Application.ScreenUpdating = True End Sub Regards, Per "robzrob" skrev i meddelelsen ... Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
Sub HideThem()
Dim r As Range Set r = Range("A1:J100").SpecialCells(xlCellTypeFormulas) Set rHide = Nothing For Each rr In r If rr.Value = 0 Then If rHide Is Nothing Then Set rHide = rr Else Set rHide = Union(rHide, rr) End If End If Next If rHide Is Nothing Then Else rHide.EntireRow.Hidden = True End If End Sub -- Gary''s Student - gsnu201001 "robzrob" wrote: Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
Hi,
try this Sub Sonic() Dim MyRange As Range Dim RowRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:J" & LastRow) For Each c In MyRange.Rows Set RowRange = MyRange.Rows(c.Row) For Each cl In RowRange.Cells If cl.Value < "" And cl.Value = 0 Then Rows(cl.Row).Hidden = True Exit For End If Next cl Next c End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "robzrob" wrote: Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
On Jan 30, 8:16*pm, "Per Jessen" wrote:
Hi Try this: Sub aaa() Dim targetRange As Range Dim tRange As Range Application.ScreenUpdating = False Set targetRange = Range("A1:J100") For Each r In targetRange.Rows * * Set tRange = targetRange.Rows(r.Row) * * For Each cell In tRange.Cells * * * * If cell.Value = 0 Then * * * * * * Rows(cell.Row).Hidden = True * * * * * * Exit For * * * * End If * * Next Next Application.ScreenUpdating = True End Sub Regards, Per "robzrob" skrev i ... Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. *If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers- Hide quoted text - - Show quoted text - Thanks. But it comes to a halt on: If cell.Value = 0 Then Perhaps I should say that some of the cell values currently return #REF! (but normally they would either be text or 0 or blank and also that I don't want to test for blank or "", only 0. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
On Jan 30, 8:24*pm, Gary''s Student
wrote: Sub HideThem() Dim r As Range Set r = Range("A1:J100").SpecialCells(xlCellTypeFormulas) Set rHide = Nothing For Each rr In r * * If rr.Value = 0 Then * * * * If rHide Is Nothing Then * * * * * * Set rHide = rr * * * * Else * * * * * * Set rHide = Union(rHide, rr) * * * * End If * * End If Next If rHide Is Nothing Then Else * * rHide.EntireRow.Hidden = True End If End Sub -- Gary''s Student - gsnu201001 "robzrob" wrote: Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. *If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers .- Hide quoted text - - Show quoted text - Thanks. This hides the rows, then I can't seem to Unhide - they appear to be deleted. And it's hiding rows with #REF! and blank in them too - but I only want to test for 0. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
I note from your reply to Per you have REF errors, this copes with those and
blank cells Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:J" & LastRow) For Each c In MyRange.Rows Set RowRange = MyRange.Rows(c.Row) For Each cl In RowRange.Cells If Not IsError(cl.Value) Then If cl.Value < "" And cl.Value = 0 Then Rows(cl.Row).Hidden = True Exit For End If End If Next cl Next c End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, try this Sub Sonic() Dim MyRange As Range Dim RowRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:J" & LastRow) For Each c In MyRange.Rows Set RowRange = MyRange.Rows(c.Row) For Each cl In RowRange.Cells If cl.Value < "" And cl.Value = 0 Then Rows(cl.Row).Hidden = True Exit For End If Next cl Next c End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "robzrob" wrote: Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row If Any Cell In It = 0
On Jan 30, 10:31*pm, Mike H wrote:
I note from your reply to Per you have REF errors, this copes with those and blank cells Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:J" & LastRow) For Each c In MyRange.Rows * * Set RowRange = MyRange.Rows(c.Row) * * For Each cl In RowRange.Cells * * * * If Not IsError(cl.Value) Then * * * * If cl.Value < "" And cl.Value = 0 Then * * * * * * Rows(cl.Row).Hidden = True * * * * * * Exit For * * * * End If * * * * End If * * Next cl Next c End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, try this Sub Sonic() Dim MyRange As Range Dim RowRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:J" & LastRow) For Each c In MyRange.Rows * * Set RowRange = MyRange.Rows(c.Row) * * For Each cl In RowRange.Cells * * * * If cl.Value < "" And cl.Value = 0 Then * * * * * * Rows(cl.Row).Hidden = True * * * * * * Exit For * * * * End If * * Next cl Next c End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "robzrob" wrote: Hello All Searched high and low amongst the forums for this and found some close solutions, but can't seem to make any work. I have Rows 1-100 & Cols A-J all with formulas or text in them. *If the result of any of the formulas in any of the that range is 0, I want the entire row hidden. Cheers .- Hide quoted text - - Show quoted text - Thanks - that's doing it great! |
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 | |||
Hide when cell value (x) is < 1 but -1 | Excel Programming | |||
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 | |||
How to hide a particular cell....... | Excel Discussion (Misc queries) |