Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
hide multiple rows based on condition within rows | Excel Programming | |||
Specify which rows to NOT hide, and have excel hide the rest | Excel Programming |