Hide rows with zero balances

[LennieLou]

Hi, I have created a report by linking the worksheet with the balances to a
new worksheet as such: Account Name, Account Number, Dr & Cr. The Dr and Cr
columns contain the value of the calculation performed on the previous
worksheet. eg C8=CALCULATIONS!O10. I am looking for a function/formula that
will hide a row when the Dr(C8) and Cr(D8) balances are zero?

Any help would be appreciated.

 

[Roger Govier]

Hi

I don't think you can achieve that with a function.
The following VBA macro will achieve what you want

Sub Hiderows()
Dim lr As Long, i As Long
With ActiveSheet
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next
End With
End Sub

And to make the hidden rows visible again, run this macro


Sub Showrows()
Dim lr As Long, i As Long
With ActiveSheet
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then
Cells(i, 1).EntireRow.Hidden = False
End If
Next
End With
End Sub

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

David McRitchie has lots of useful help on his site to get you started
with installing code, at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then on your sheet with the data, just choose
Tools>Macro>select either to Hiderows or Showrows>Run

 

[LennieLou]

Hi Roger,

I know absolutely nothing about macro's but with your information my report
now works like a charm. I am in awe of your excel knowledge.

Thank you so much.

 

[KevinK]

Hi Roger

I found this macro very useful, except it is hiding rows with values in
them. My worksheet has approx ten columns with text in the first column and
numbers/percentages in the next 9 columns. I want it to hide the rows with
zero values after the first column. There are about 100 rows in each
tab/worksheet. Also, how do I get the macro to run across all
tabs/worksheets in the same file?

Thanks
Kevin

 

[Gord Dibben]

All 9 columns have a zero or any of the 9 have a zero?

Will the number of "about 100 rows" be variable?

Please respond so's we can tailor code to suit for all sheets in a workbook.


Gord Dibben MS Excel MVP

 

[KevinK]

All columns must have a zero. Actually there are at least 11 columns excl
the first with text. The rows will be variable. Is it possible to make the
columns variable as well?

Thanks
KevinK

 

[Gord Dibben]

Variable columns?

You mean some rows have fewer or more columns than others?

This macro assumes the same number(numcols) of used columns in each row.

Sub hide_zero_rows()
Dim numcols As Long
Dim lrow As Long
Dim prow As Range
Dim StartRow As Long
Dim EndRow As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Cells.EntireRow.Hidden = False
StartRow = 1
Range("A1").Select
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
For i = EndRow To StartRow Step -1
Set prow = Range(Cells(i, "B"), _
Cells(i, LastCol))
prow.Select
numcols = prow.Columns.Count
If Application.CountIf(prow, "0") = numcols Then
prow.EntireRow.Hidden = True
End If
Next
Range("A1").Select
Next ws
End Sub


Gord

 

[KevinK]

Hi Gord
The macro seems to run over all worksheets in my file, but no rows are
hidden at the end. Just to rehash, there are variable rows in each tab and
12 columns(text in the 1st col and either numbers or percentages in the
remaining 11 col's). There are headings at the top of each column such as
Descript(1st col), Actual(2nd col), Budget(3rd col), Variance, %Var, etc.
Thanks
KevinK

 

[Tasha]

Hi Gord....wondering if you can look at this for me? Was working fine until
I added a column, now isn't...Want it to hide rows if columns D-H have 0's in
them. Also, need this changed so it will only do this on the active
worksheet, not all worksheets in the workbook....can you help me? Would
really appreciate it!!!

Dim numcols As Long
Dim lrow As Long
Dim prow As Range
Dim StartRow As Long
Dim EndRow As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Cells.EntireRow.Hidden = False
StartRow = 1
Range("A1").Select
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
For i = EndRow To StartRow Step -1
Set prow = Range(Cells(i, "D"), _
Cells(i, LastCol))
prow.Select
numcols = prow.Columns.Count
If Application.CountIf(prow, "0") = numcols Then
prow.EntireRow.Hidden = True
End If
Next
Range("A1").Select
Next ws

 

[Tasha]

actually, I said that wrong, I need rows hidden if columns D-G only have 0's
in them. Column H may have data in it, but if columns D-G have 0's I need H
hidden.

 

[Gord Dibben]

Will column A be the same length as columns D:H?

Will you have columns past column H?

Assuming answer to both above is Yes.............

Sub test()
Dim lrow As Long
Dim prow As Range
Dim StartRow As Long
Dim EndRow As Long
Sheets("Sheet6").Activate 'adjust sheetname to suit
Cells.EntireRow.Hidden = False
StartRow = 1
Range("A1").Select
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = EndRow To StartRow Step -1
Set prow = Range(Cells(i, "D"), Cells(i, "H"))
With prow
If Application.CountIf(prow, "0") = 5 Then 'D:H is 5 columns
.EntireRow.Hidden = True
End If
End With
Next
Range("A1").Select
End Sub


Gord