PDA

View Full Version : Solved: Hide zero rows



alok2007
06-19-2007, 02:05 AM
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
06-19-2007, 02:54 AM
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

alok2007
06-19-2007, 03:01 AM
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
06-19-2007, 03:12 AM
So you want a toggle



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

alok2007
06-19-2007, 03:22 AM
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
06-19-2007, 03:38 AM
Post your workbook to save me guessing.

alok2007
06-19-2007, 03:49 AM
Wb is attached.

Bob Phillips
06-19-2007, 07:14 AM
I don't understand the comments in column E, you haven't mentione A, B , C codes before.

alok2007
06-19-2007, 09:42 PM
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
06-19-2007, 10:08 PM
Dear alok2007, it seems that your requirement are same to those of a post available on www.excelforum.com (http://www.excelforum.com) The matter is solved there now, you refer the same to save the time of other volunteers at this forum

alok2007
06-19-2007, 10:31 PM
checked and tried the code . It seems OK for me and will need slight modification which I have made.