Hiding Rows when result of formual is 0

June 16, 2009 at 19:33:26
Specs: Windows XP
Hi,

I am using Excel 2007 and want to write a
macro that will look at the values in the cell
range B31:B95; if the value is 0, I want to hide
the row. Value is formula driven. I can get it
to evaluate a single row and hide the row if
zero, but can't seem to get the right code for the noted range.
Any help would appreciate

Thanks,

Mike,
p.s. I am fairly new at vba


See More: Hiding Rows when result of formual is 0

Report •


#1
June 16, 2009 at 20:50:29
For rw = 35 To 95
 If Cells(rw, "B") = 0 Then Cells(rw, "B").EntireRow.Hidden = True
Next


Report •

#2
June 16, 2009 at 21:01:36
Thanks for the quick reply... I have a beginners question, is this
entered in excel as an equation, or in the VBA code? is it a
private sub or general?

Thanks again


Report •

#3
June 17, 2009 at 04:49:52
You said you were able to write a macro to hide a single row "but can't seem to get the right code for the noted range".

I have provided that code.

What I provided was a For-Next loop that should replace the VBA code you used to hide the single line.

Perhaps you should post the code you used so we can be sure we're comparing apples to apples.


Report •

Related Solutions

#4
June 17, 2009 at 09:20:35
This is thecode I came up with and it seems to be working:

Private Sub Worksheet_Calculate()

Dim lastRow As Long, myRange As Range

Application.Calculation = xlCalculationManual

lastRow = Cells(Rows.Count, 2).End(xlUp).Row

Rows.Hidden = False

For Each myRange In Range("B11:B75")

If myRange.Value = 0 Then myRange.EntireRow.Hidden = True

Next myRange

End Sub

I have also created a macro to run the code when a value in specified cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$4" Then
Application.Run "Macro4"
End If
End Sub

But now I need to add another range to the hide code above (B83:B147). I have tried copy/paste the original code and updating the range, but it does not work. I have also increased the range in the first code through B147, but it hides everthing below the first "0" value in the top of the range. Any suggestions?

Thanks

Mike


Report •

#5
June 18, 2009 at 06:58:21
Hi again, just checking if anyone can help me modify my
code to work in two distinct ranges. Current code is:
Private Sub Worksheet_Calculate()

Dim lastRow As Long, myRange As Range

Application.Calculation = xlCalculationManual

lastRow = Cells(Rows.Count, 2).End(xlUp).Row

Rows.Hidden = False

For Each myRange In Range("B11:B75")

If myRange.Value = 0 Then myRange.EntireRow.Hidden =
True

Next myRange

End Sub


I would also like to look at B83:B147 and hide rows where formula
results in 0 in any cell of this range.

Thanks

Mike


Report •

#6
June 18, 2009 at 07:45:41
First, I'm a little confused by your Worksheet_Change macro.

You said "I have also created a macro to run the code when a value in specified cell changes"

Your Worksheet_Change calls "Macro4" but I don't see any macro named "Macro4". Is there a reason that you shared that information with us?

Second, just an observation: I assume that you are aware that your code sets Calculation to Manual and that you never reset it to Automatic. That could cause problems later on.

Finally, as far as extending the range, try this:

For Each myRange In Range("B11:B75,B83:B147")


Report •

#7
June 18, 2009 at 08:00:33
Derby,

Thanks for the feedback.

I noticed that it was on manual, but couldn't figure out why.
Now that you mention it, I can see it in the code. I am
assuming I can simply change the from
Application.Calculation = xlCalculationManual
to
Application.Calculation = xlCalculationAutomatic

I tried changing it in the excel calculations options, but it
would always default back to manual.

Also, you can ignore the macro4. I could not figure out how to
run the change macro when a cell was changed, so I recorded
macro while hitting F9 to calculate.

I will try the range you noted.

Thanks again for the help

Mike


Report •


Ask Question