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

On Sun, 1 Mar 2009 23:18:01 -0800, KevinK
wrote:

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

"Roger Govier" wrote:

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
ToolsMacroselect either to Hiderows or ShowrowsRun

--
Regards

Roger Govier


"LennieLou" wrote in message
...
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.