Solved: Hide a Row when Formula is "True or False"

[070968]

Hi

Please solve my following problem :-

Hide a Row when Formula is "True or False".


Example : if cell Sheet1!B2="yes"
then, Hide Sheet2!entire 3rd row


Is it possible to do this through following function :

[VBA]
Function FunctionHide(lvRow As Double, lvTrue As Boolean)
Dim lvRange As Range
If lvTrue = True Then
Set lvRange = Range("A1").Offset(lvRow - 1)
lvRange.EntireRow.Hidden = True
End If
McHide = lvTrue
End Function
[/VBA]


I use the above condition in many rows of my workbook. Please help me.


regards!


Garima

[vonpookie]

You can't use a function to hide a row or column, no.

However, if the true/false value will always be determined by a formula in one column, you could use a Worksheet_Calculate event to loop through the rows and hide/unhide rows accordingly.

How are you determining which row(s) to hide?

[070968]

Hi

Sheet1!d2 is user defined cell in which he will put "yes" or "no"

If it is "no" then I want to hide a Row say Sheet2! Row 5

Please Help. I am new to VBA. Please can u give me full written code.

Thanks

Garima

[vonpookie]

Well, it's easy enough to write code for that one particular instance, but if you want to check each row of data for "yes" or "no" and hide rows--I'm afraid I don't understand the logic behind it.

So if you are entering "yes" into D2, why hide row *5* on the other sheet? It can be done, but will it always be hiding the row that is 3 down from the row the "yes" appears on in the other sheet? This is where I am confused at the moment.

[070968]

Please see attached file.

I have written a note in yellow marked cell in Sheet "DR"

Is it possible ?

Thanks

Garima

[vonpookie]

Ok, I think I see what you're doing now.

Put this code in the module for sheet "DR". If you right-click the sheet tab and select "View code" the Visual Basic Editor will automatically open and display the correct module. Just paste this code into the blank window it displays.

[vba]Private Sub Worksheet_Calculate()
Dim c As Range

For Each c In Range("B40:B41,B43")
If c.Value = "no" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c

End Sub[/vba]

This will run whenever the formulas on sheet "DR" calculate. On this sheet, if B40, B41 or B43 ="no" then the row will be hidden. If they recalculate again and do not ="no" then the rows will be unhidden.

[070968]

Thanks so much for your kind help !!!.



Code worked.

Is it possible to auto run this code, when value changes in the relevent cells?


best regards,


Garima

[vonpookie]

That's what it should do, if I'm understanding you correctly.

The formulas on Sheet DR refer to the other sheets. When the values on those sheets change, it will cause any formulas that refer to them to recalculate. If those formulas happen to be on Sheet DR, it will automatically run the Worksheet_Calculate code that was placed in the module for that sheet.

Try changing the values on Sheet "AT" and see if any of the 3 rows are hidden on sheet "DR" when you go back and look at it

[070968]

HI

Now I am using the code successfully, It is working automatically. I Tried all options of my file to check it.

Thanks for ur valuable co-operation.

I am sooooooooooooooooooo happyyyyyyyy


regards!


Garima