Solved: Hide zero rows

[alok2007]

During net search, have found a code to hide/unhide rows with zero values. The code is made for a command button. can this be made a simple macro ie without using command button?

Also when run this hides all rows ?

Private Sub CommandButton1_Click()
Static flag As Boolean
If flag Then
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
  If Cells(i, 3) = 0 Or Cells(i, 4) = 0 Or Cells(i, 5) = 0 Then
   Cells(i, 1).EntireRow.Hidden = True
  End If
  Next
Else
ActiveSheet.Rows.Hidden = False
End If
flag = Not flag
End Sub

Can someone please modify the same?

[Bob Phillips]

[vba]

Sub HideRows()
With ActiveSheet
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
.Rows(i).Hidden = (.Cells(i, 3) = 0 Or _
.Cells(i, 4) = 0 Or _
.Cells(i, 5) = 0)
Next i
End With
End Sub
[/vba]

[alok2007]

On running the code ,rows upto last record is hide by the code. On second click, one more row is hiding instead of unhiding the rows as the original code is doing.

[Bob Phillips]

So you want a toggle

[vba]

Sub HideRows()
Dim i As Long
With ActiveSheet
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
If .Cells(i, 3) = 0 Or .Cells(i, 4) = 0 Or .Cells(i, 5) = 0 Then
.Rows(i).Hidden = Not .Rows(i).Hidden
End If
Next i
End With
End Sub
[/vba]

[alok2007]

Still the code is not hiding rows with 0 value it is hiding all the rows. Regarding unhiding, though it is unhiding the hided rows but at the same time it is hiding row just below the last record.

[Bob Phillips]

Post your workbook to save me guessing.

[alok2007]

Wb is attached.

[Bob Phillips]

I don't understand the comments in column E, you haven't mentione A, B , C codes before.

[alok2007]

THis is an indication only for changes in cell C1. As C1 is a drop down list, by which you can select name of the employees and his data will be shown. So when you will select "A" in C1 it will display all the rows including those with Zero values but those zero rows are relevant for "A" so these zero rows should not be printed. Similarly, if you select "B" in cell C1 you will get some rows with zero values, but these rows are not relevant to "B" so these rows should also not be printed.

and that is why i want that on once click the code will hide the zero rows & on next click it will unhide the rows so that whenever there will be any change in cell C1, zero rows will hide.

[sujittalukde]

Dear alok2007, it seems that your requirement are same to those of a post available on www.excelforum.com The matter is solved there now, you refer the same to save the time of other volunteers at this forum

[alok2007]

checked and tried the code . It seems OK for me and will need slight modification which I have made.