Hide rows based on output from formula

mustang70

New Member
Joined
Oct 30, 2013
Messages
15
Hi I have searched many forums and can't seem to find my answer to this problem

I am working a large quoting program and I need certain rows to automatically hide when I have a cell condition based on a number i.e. if P30=1 I want the row hidden and if P30=2 I want the rows to appear. The numbers can be changed to "true" or "false" if it would work better.
Currently this formula works but only if I got to the P30 cell and click and hit enter - the cell out put is from a differnt sheet and I don't know if that the problem. I need this formual to run all of the time as I make changes to the first sheet and this data is on sheet 4 - this data has a Macro which copys the info and pastes this in Word
I have also tried Autofill with no luck.
There are about 10 other differnt rows I need to hide based on formual outputs - Ideas?

Here is the current formula - only works if I go to the sheet and manauly type 1 or 2 and hit enter, does not work from a formula output

Private Sub Worksheet_Change(ByVal target As Range)
If Range("s30").Value = 1 Then
Rows("34:35").Select
Selection.EntireRow.Hidden = True
Range("s30").Select
ElseIf Range("s30").Value = 2 Then
Rows("34:35").Select
Selection.EntireRow.Hidden = False
Range("s30").Select
End If

End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
The Worksheet_Change event procedure is not triggered by formulas.

You could use the Worksheet_Change event procedure in the other sheet and have it Show\Hide rows on this sheet when you make changes to the other sheet.

Otherwise, is it just the one formula in S30, or will you want to Show\Hide other rows based on other formulas? If other formulas, please explain all conditions and their rows. What is the formula in S30?
 
Upvote 0
The Worksheet_Change event procedure is not triggered by formulas.

You could use the Worksheet_Change event procedure in the other sheet and have it Show\Hide rows on this sheet when you make changes to the other sheet.

Otherwise, is it just the one formula in S30, or will you want to Show\Hide other rows based on other formulas? If other formulas, please explain all conditions and their rows. What is the formula in S30?

Bascially the formula in S30 is an IF formula and if a value is true I get a 1 and if it's False a 2. (this is on sheet4, all of the work I do is on sheet1 but I have a macro button on sheet 1 that copies part of sheet4)
Again I am trying to copy the data on sheet4 and send to Word and this is why I want the rows hidden or it leaves blanks on my quote.

I will have to hide other rows on sheet 4 based on formulas on sheet 1 - probably about 6 or 7 different rows
 
Upvote 0
Bascially the formula in S30 is an IF formula and if a value is true I get a 1 and if it's False a 2. (this is on sheet4, all of the work I do is on sheet1 but I have a macro button on sheet 1 that copies part of sheet4)
Again I am trying to copy the data on sheet4 and send to Word and this is why I want the rows hidden or it leaves blanks on my quote.

I will have to hide other rows on sheet 4 based on formulas on sheet 1 - probably about 6 or 7 different rows

What is the S30 formula ? The actual formula. Not just a description that it's an IF formula.
 
Upvote 0
=IF(sheet1!U9=TRUE,2,1)

Again I made this formual just to try to get the hidden row to work but sheet1!U9 is the actual cell I need to read
 
Upvote 0
=IF(sheet1!U9=TRUE,2,1)

Again I made this formual just to try to get the hidden row to work but sheet1!U9 is the actual cell I need to read

I understand what you want done. Now what is the formula in Sheet1 U9?

I'm trying to determine what specifically the user does that triggers the formulas to calculate which in turn will show\hide rows. If that can be determined, then the code can trigger off of the user changing the sheet 1. Does that make sense.
 
Upvote 0
U9 is the output of an option button from sheet1- there are 3 option buttons on this output - basically if this cell is true from the option button I want to hide the cells on sheet 4

I don't think that I would want the option button tied direclty to a VBA code to hide the cells - it also changes other formuals on my Excel program
 
Upvote 0
U9 is the output of an option button from sheet1- there are 3 option buttons on this output - basically if this cell is true from the option button I want to hide the cells on sheet 4

I don't think that I would want the option button tied direclty to a VBA code to hide the cells - it also changes other formuals on my Excel program

Why not tie VBA directly to the option button? That's what you are trying to do but unsuccessfully with the S30 formula. You can still have the "other" formulas for whatever they do.

Put something like this in the Sheet1 code module to show\hide rows on sheet 4 when the option button changes.

Adjust the OptonButton name to suit. Remove the Not if I have the show\hide rows reversed from what you want.

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [B]OptionButton1[/B]_Change()
    Sheets("Sheet4").Rows("34:35").Hidden = [COLOR=darkblue][B]Not[/B][/COLOR] Range("U9").Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
That does work great but on my program but I have 2 other buttons that change rows 33, 34 or 35 depending on the pressed button

I have some thinking to do now
Basically I need option button 1 which is tied to U9 to hide row 34 and 35 only and option button 2 which is tied to V11 to show row 33 only and option button 3 which is tied to T12 to to hide rows 33, 34 and 35
To make matters even more complicated rows 92-94 and rows 152-154 have to mimic the same function- this program runs three different quotes on sheet4 at the same time
 
Upvote 0
That does work great but on my program but I have 2 other buttons that change rows 33, 34 or 35 depending on the pressed button

I have some thinking to do now
Basically I need option button 1 which is tied to U9 to hide row 34 and 35 only and option button 2 which is tied to V11 to show row 33 only and option button 3 which is tied to T12 to to hide rows 33, 34 and 35
To make matters even more complicated rows 92-94 and rows 152-154 have to mimic the same function- this program runs three different quotes on sheet4 at the same time

The logic is confusing...
option button 1 which is tied to U9 to hide row 34 and 35 only
option button 2 which is tied to V11 to show row 33 only
option button 3 which is tied to T12 to to hide rows 33, 34 and 35

It looks like OptBut 1 and 2 do the same thing. Did you mean OptBut 2 to hide row 33 only?

I take it that there is no case to show all three rows at the same time?

Do rows 92-94 and 152-154 have their own option buttons or do thay use the same three option buttons with the same logic?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top