Solved: Excel 2007 Hide Row vba

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Technodruid

Thread Starter
Joined
Sep 8, 2010
Messages
72
Hey, I need to Hide Row's 34-47 Based on a summed Value in Y34 -Y47. IE if Y34 = 0 then Row 34 is hidden, if Y35 = 0 then 35 is hidden Ive tried this with a couple different code and it keeps erroring. Error 425
Code:
Private Sub Worksheet_Calculate(ByVal Target As Range)
If Target.Address = "$Y$34" Then
If IsNumeric(Target) Then
Rows(34).EntireRow.Hidden = (Target.Value = 0)
End If
End If
End Sub
Does Any one Have a simple way to Hide the rows? They will always be 34-47 and Based on Y34 -Y47.
 
Joined
Jul 1, 2005
Messages
8,546
Maybe this?

Private Sub Worksheet_Calculate()
For Each Cell In Range("Y34:Y47")
If Cell.Value = 0 Then
Rows(Cell.Row).Hidden = True
Else
Rows(Cell.Row).Hidden = False
End If
Next Cell
End Sub

(BUT: if the (e.g.) Y34 summed value references another cell in row 34, how will you edit it once it's hidden?)
 

Technodruid

Thread Starter
Joined
Sep 8, 2010
Messages
72
Thanks Bomb, Code worked almost perfect. I had to turn off auto calculate and put calculate in to the workbook code below my refresh otherwise it tried to run forever.
 

Technodruid

Thread Starter
Joined
Sep 8, 2010
Messages
72
Okay, I need to find a fix for this that doesnt require me to remove the Auto Calc feature. So had to unsolve it.
 
Joined
Sep 4, 2003
Messages
4,912
Try disabling events like I've shown in the code below.

Code:
Private Sub Worksheet_Calculate()

[B]Application.EnableEvents = False[/B]
For Each Cell In Range("Y34:Y47")
If Cell.Value = 0 Then
Rows(Cell.Row).Hidden = True
Else
Rows(Cell.Row).Hidden = False
End If
Next Cell
[B]Application.EnableEvents = True[/B]
End Sub
Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top