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

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

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
.

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
.

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.

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.

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
.

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!