Problem with hide/unhide macro

[celia]

I have this code below suppose to hide the the whole row if any zer
values found in column D between D6 till D20
However, the code wouldn't work if any zero are found after row 11.
mean it can hide the rows if the zero are found before row 11. Can tr
out once the total sum are greater.

Please help me with the code.

Sub HideZeros1()
Dim IsHidden As Boolean
Dim i As Variant
i = 1
Dim RowsCount As Integer

Range("D66").Select
Selection.CurrentRegion.Select
RowsCount = Selection.Rows.Count

IsHidden = Selection.Range("D620").EntireRow.Hidden
For i = 1 To RowsCount
Range("D66").Offset(i, 0).Select
If ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = Not IsHidden
End If
Next
End Sub

Thanks,
celia


p/s can some one explain the line code starting from "IsHidden

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52844

 

[Jim Rech]

I think your macro is a bit convoluted. I'd rather just do a new one than
critique it.

If your purpose is to hide any rows where the value in the range D6 to D20
is 0 or blank, you can do it like this:

Sub HideRows()
Dim Cell As Range
With Range("D620")
.EntireRow.Hidden = False
For Each Cell In .Cells
If Cell.Value = 0 Then Cell.EntireRow.Hidden = True
Next
End With
End Sub


--
Jim Rech
Excel MVP
|I have this code below suppose to hide the the whole row if any zero
| values found in column D between D6 till D20
| However, the code wouldn't work if any zero are found after row 11. I
| mean it can hide the rows if the zero are found before row 11. Can try
| out once the total sum are greater.
|
| Please help me with the code.
|
| Sub HideZeros1()
| Dim IsHidden As Boolean
| Dim i As Variant
| i = 1
| Dim RowsCount As Integer
|
| Range("D66").Select
| Selection.CurrentRegion.Select
| RowsCount = Selection.Rows.Count
|
| IsHidden = Selection.Range("D620").EntireRow.Hidden
| For i = 1 To RowsCount
| Range("D66").Offset(i, 0).Select
| If ActiveCell.Value = 0 Then
| Selection.EntireRow.Hidden = Not IsHidden
| End If
| Next
| End Sub
|
| Thanks,
| celia
|
|
| p/s can some one explain the line code starting from "IsHidden"
|
| Attachment filename: book1.xls
| Download attachment:
http://www.excelforum.com/attachment.php?postid=528447
| ---
| Message posted
|

 

[celia]

Actually, with my code, I want to be able to hide the rows when I clic
the button and unhide it when I click the button again.

My problem now is when the zeros are found after row11, i can only hid
the rows and cannot unhide it when the button is clicked again.

Hope someone understand my problems and give me some help.

Thanks a lot,
celi

 

[celia]

Hi there,

Can anyone have a look at my code and help me to improve it?

I want my code to enable the button to hide zeros and unhide it if th
button is clicked again. My problem is it would not work properly if
larger amout of cost are entered.

Thanks for any help,
celi

 

[Dave Peterson]

I used a button from the Forms toolbar and assigned it this macro:

Option Explicit
Sub HideShowRows()

Dim BTN As Button
Dim Cell As Range

Set BTN = ActiveSheet.Buttons(Application.Caller)

With Range("D620")
.EntireRow.Hidden = False
If LCase(BTN.Caption) = "hide rows" Then
For Each Cell In .Cells
If Cell.Value = 0 Then Cell.EntireRow.Hidden = True
Next Cell
BTN.Caption = "Show Rows"
Else
BTN.Caption = "Hide Rows"
End If
End With

End Sub

 

[celia]

Dave,

Thanks, your code work well. I just wonder why my code didn't work th
same? Anyway, thankyou.


have a nice day,
celi

 

[Dave Peterson]

If you post your code, I'm sure you'll get some explanations.