Forum Home
Press F1
 
Thread ID: 73942 2006-11-06 11:20:00 excel macro to hide rows bpt2 (6653) Press F1
Post ID Timestamp Content User
496824 2006-11-06 11:20:00 I have a budget spreadsheet which includes a number of rows (budget items) that have zero values. I would like to print the spreadsheet without these zero value rows. What macro can I run? bpt2 (6653)
496825 2006-11-06 16:38:00 Hi, for macro's you need to be a bit more specific like which columns in particular or if no particular columns then do you mean if the sum of the whole row = 0? You could just use AutoFilter but if there are >3 columns to determine your criteria then create an extra column with an appropriate formula and filter off this column. Parry (5696)
496826 2006-11-07 11:18:00 basically I have a number of budget items (by column) with zero entries over a number of years (rows) which I don't want to print.
I think I got it with this:

Sub HideRows()
With Range("c2:z800")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Not sure that I understand it properly but it seems to work. 		bpt2 (6653)
496827 2006-11-08 23:52:00 basically I have a number of budget items (by column) with zero entries over a number of years (rows) which I don't want to print.
I think I got it with this:

Sub HideRows()
With Range("c2:z800")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Not sure that I understand it properly but it seems to work.


Yeah that looks like it should work ok as long as when you are printing you have it set so it doesn't print hidden rows/columns etc.

the With... End With statements say what actions to carry out on the range C2:Z800 which are:

1. make all the rows visible (set the hidden property to false for each row)
2. Loop through all the rows in the range and sum the values of each row (uses a For i = ... Next i loop. i begins with the value after the = and increases by 1 each loop up to .Rows.Count, the number of rows in the range)
3. Check if the sum is zero and if it is then hide that row. (make the hidden property true).

HTH, Parry might be able to help more, he helped me to get this far. 		pico (4752)
1