Hide rows with value of 0

[chrismv48]

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.

 

[JMay]

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

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

HTH

 

[Bernard Liengme]

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

 

[Mike H]

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]

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.

 

[chrismv48]

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

 

[Bernard Liengme]

Sorry, but it works for me