Hide rows in multiple columns based on zero values

charlesdh · #16 06-19-2015, 10:37 AM

HI,

Are you really looking for "0"? The word doc does not show "0".
Now you have a column for "Budget Sheet Total" is this a sum for each roll?.

If so you can key in on it and then hide the rows.
As mentioned before it may have saved a lot of time if you posted a copy of your file.

Deane · #17 06-21-2015, 11:36 PM

The excel file exceeds the limit of this forum..

Thats why I havent posted it?

To give you a bit of background on the sheet. The sheet pulls new data every month and certain rows are always blank so in order to only show rows with values and therefore make the sheet more compact I need to hide zeros. The word doc does show zeros as '-'. No the budget sheet total is not the sum of the previous columns.

But I see what you mean.. maybe if I make a column that sums all of the columns I can just use the code on that column only? could you provide a code for this? say in column T.

Really appreciate the help thus far.

charlesdh · #18 06-22-2015, 09:59 AM

HI,

I'll see what I can do. But, can you try to zip the file?

charlesdh · #19 06-23-2015, 12:41 PM

HI,

Here's a code that may work. You will need to make change to it to reflect you ranges.

Code:

Sub Hide_0()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim r As Range, x, c As Range
Dim r1 As Range
Dim n As Long, n1 As Long
Dim lrow As Long
Application.ScreenUpdating = False
ActiveSheet.Rows.Hidden = False
lrow = Range("ao65536").End(xlUp).Row
Set x = Range("AO5:AO" & lrow)
x.EntireRow.Hidden = True

For Each c In x
Set r = Range("AO" & c.Row & ":As" & c.Row)
Set r1 = Range("AU" & c.Row & ":AZ" & c.Row)
    n = Application.Sum(r)
    n1 = Application.Sum(r1)
    If n <> "0" And n1 <> "0" Then
        c.EntireRow.Hidden = False
    End If
Next c
End Sub

Deane · #20 06-23-2015, 11:24 PM

Thx Charles! I finally have it working.

Thx for all the help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code to Hide columns based on selection on prior sheet	Silver1379	Excel Programming	0	04-15-2015 08:40 AM
I need to add multiple values based on multiple criteria in a cell not sure what to do	AUHAMM	Excel	3	10-27-2014 09:11 PM
Insert values from multiple rows based on value in one column	pachmarhi	Excel	3	07-18-2014 09:57 PM
Hide Rows and Update Chart based on cell value	ubns	Excel Programming	5	05-07-2012 05:44 AM
Conditional Formatting to Hide Rows or Columns?	sczegus	Excel	0	09-26-2006 04:17 PM