Hide rows with value of 0
 

Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from using
filters. In addition to the rows already hidden, I'd like to hide the rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Copy Visible Cells Only to New Worksheet (Edit, Goto, Special...)

Then Auto-Filter (Using Custom where N & R Columns <0

HTH

"chrismv48" wrote:

Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from using
filters. In addition to the rows already hidden, I'd like to hide the rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Here is a start
Sub tryme()
mylast = Cells(Cells.Rows.Count, "N").End(xlUp).Row
For j = 1 To mylast
If Cells(j, "N") = 0 and Cells(j, "R")= 0 Then
Cells(j, "N").EntireRow.Hidden = True
End If
Next j
End Sub

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chrismv48" wrote in message
...
Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from
using
filters. In addition to the rows already hidden, I'd like to hide the
rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Hi,

Try this as worksheet code

Sub stantial()
Dim MyRange, CopyRange As Range
Lastrow = Cells(Cells.Rows.Count, "N").End(xlUp).Row
Set MyRange = Range("N1:N" & Lastrow)
For Each c In MyRange
If Not IsEmpty(c) And c.Value = 0 And Not _
IsEmpty(c.Offset(, 4)) And c.Offset(, 4).Value = 0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.EntireRow.Hidden = True
End If
End Sub

Mike

"chrismv48" wrote:

Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from using
filters. In addition to the rows already hidden, I'd like to hide the rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Thanks for the start. Unfortunately this crashed Excel. I'm not sure why
but it looks like it was hung on calculating and couldn't exit the program.

To the other poster: Using a filter and/or copying the sheets around is
more effort than just hiding the rows by hand because the data is formatted
in a particular way that makes it necessary to keep the hidden rows etc.
Thanks though.

"Bernard Liengme" wrote:

Here is a start
Sub tryme()
mylast = Cells(Cells.Rows.Count, "N").End(xlUp).Row
For j = 1 To mylast
If Cells(j, "N") = 0 and Cells(j, "R")= 0 Then
Cells(j, "N").EntireRow.Hidden = True
End If
Next j
End Sub

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chrismv48" wrote in message
...
Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from
using
filters. In addition to the rows already hidden, I'd like to hide the
rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Hi Mike,

Thanks! This worked very well. Not to be picky, it seemed to disregard my
selection and appy the macro to the whole sheet...is this what the code
intends? Perhaps I'm doing something wrong?

Again, just a small tweak and I can live with it the way it is, but just
wanted to check with you on this.

Thanks again

"Mike H" wrote:

Hi,

Try this as worksheet code

Sub stantial()
Dim MyRange, CopyRange As Range
Lastrow = Cells(Cells.Rows.Count, "N").End(xlUp).Row
Set MyRange = Range("N1:N" & Lastrow)
For Each c In MyRange
If Not IsEmpty(c) And c.Value = 0 And Not _
IsEmpty(c.Offset(, 4)) And c.Offset(, 4).Value = 0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.EntireRow.Hidden = True
End If
End Sub

Mike

"chrismv48" wrote:

Hello,

This is a fairly common question relating to hiding rows, but mine comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from using
filters. In addition to the rows already hidden, I'd like to hide the rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.

Hide rows with value of 0
 

Sorry, but it works for me
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chrismv48" wrote in message
...
Thanks for the start. Unfortunately this crashed Excel. I'm not sure why
but it looks like it was hung on calculating and couldn't exit the
program.

To the other poster: Using a filter and/or copying the sheets around is
more effort than just hiding the rows by hand because the data is
formatted
in a particular way that makes it necessary to keep the hidden rows etc.
Thanks though.

"Bernard Liengme" wrote:

Here is a start
Sub tryme()
mylast = Cells(Cells.Rows.Count, "N").End(xlUp).Row
For j = 1 To mylast
If Cells(j, "N") = 0 and Cells(j, "R")= 0 Then
Cells(j, "N").EntireRow.Hidden = True
End If
Next j
End Sub

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chrismv48" wrote in message
...
Hello,

This is a fairly common question relating to hiding rows, but mine
comes
with a bit of a twist.

I get a monthly spreadsheet that is generated to have a formatting that
hides a lot of particular rows to begin with. This precludes me from
using
filters. In addition to the rows already hidden, I'd like to hide the
rows
that contain a 0 in both columns N & R.

Ideally I'd like to be able to run this macro on a selection, but all
suggestions are welcome.

Thanks.