PDA

View Full Version : [SOLVED] Conditional hiding of rows with multiple Ifs



K. Georgiadis
03-25-2005, 09:21 PM
How does one write this?


In a worksheet named "Control Panel" the user types in the descriptions of market segments in B2:B21
Using the COUNTA function, cell D1 in Sheet "Control Panel" returns the number of populated rows (some rows are likely to be blank)
Then, I want to hide the following rows in other worksheets of the workbook depending on the value returned in "Control Panel" Cell D1:


If it is 7, hide rows 10:17 and 28:35 in each of Worksheets "Sales" and "Margin"
If it is 8, hide rows 11:17 and 29:35 in each of Worksheets "Sales" and "Margin",
and so on.

Thanks!

Jacob Hilderbrand
03-25-2005, 09:59 PM
Try something like this:


Select Case Sheets("Control Panel").Range("D1").Value
Case Is = 7
Sheets("Sales").Range("A10:A17").EntireRow.RowHeight = 0
Sheets("Sales").Range("A28:A35").EntireRow.RowHeight = 0
Sheets("Margin").Range("A10:A17").EntireRow.RowHeight = 0
Sheets("Margin").Range("A28:A35").EntireRow.RowHeight = 0
Case Is = 8
Sheets("Sales").Range("A10:A17").EntireRow.RowHeight = 0
Sheets("Sales").Range("A28:A35").EntireRow.RowHeight = 0
Sheets("Margin").Range("A11:A17").EntireRow.RowHeight = 0
Sheets("Margin").Range("A29:A35").EntireRow.RowHeight = 0
End Select

Or



Select Case Sheets("Control Panel").Range("D1").Value
Case Is = 7
Sheets("Sales").Range("A10:A17").EntireRow.Hidden = True
Sheets("Sales").Range("A28:A35").EntireRow.Hidden = True
Sheets("Margin").Range("A10:A17").EntireRow.Hidden = True
Sheets("Margin").Range("A28:A35").EntireRow.Hidden = True
Case Is = 8
Sheets("Sales").Range("A11:A17").EntireRow.Hidden = True
Sheets("Sales").Range("A29:A35").EntireRow.Hidden = True
Sheets("Margin").Range("A11:A17").EntireRow.Hidden = True
Sheets("Margin").Range("A29:A35").EntireRow.Hidden = True
End Select

K. Georgiadis
03-26-2005, 07:02 AM
In a regular Module, I presume...

Thanks!

Paleo
03-26-2005, 07:44 AM
Hi K.,

you can put it in a regular module but then it will need to be fired by something and if you put it in the worksheets module you can assign it to the selection_change event:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

K. Georgiadis
03-26-2005, 07:48 AM
I tried it. There is a shortcoming: if, as an afterthought, additional rows are populated in the control panel after the macro has run, the corresponding rows in "Sales" and "Margin" remain hidden

Carlos, you mean in ThisWorkbook?

Paleo
03-26-2005, 07:51 AM
Hi K.,

well as who is "commanding" this is range D1, while its not changed the other rows wont appear. Did you change range D1 before populating that?

K. Georgiadis
03-26-2005, 08:15 AM
Range D1 is driven by Excel's COUNTA function and changes automatically as more or fewer rows in "Control Panel" column B are populated. I attached a small zip file of this experimental workbook so that you can try it for yourself: populate one more row in Column B to change Cell D1 to 8; the corresponding row will remain hidden in "Sales" and "Margin"
What I did not explain properly in my original post is that the rows should be hidden/unhidden dynamically as a function of the number of populated rows in the Control Panel, bearing in mind that the user is likely to make several edits, either adding or subtracting from Column B in the "Control Panel"

Jacob Hilderbrand
03-26-2005, 10:08 AM
Try this macro. Place the code in the "Contol Panel" sheet module.



Option Explicit

Private Sub Worksheet_Calculate()
Dim i As Long
i = Range("D1").Value
Sheets("Sales").Cells.EntireRow.Hidden = False
Sheets("Margin").Cells.EntireRow.Hidden = False
Sheets("Sales").Range("A" & 3 + i & ":A17").EntireRow.Hidden = True
Sheets("Sales").Range("A" & 21 + i & ":A35").EntireRow.Hidden = True
Sheets("Margin").Range("A" & 3 + i & ":A17").EntireRow.Hidden = True
Sheets("Margin").Range("A" & 21 + i & ":A35").EntireRow.Hidden = True
End Sub

K. Georgiadis
03-26-2005, 12:00 PM
I will, thanks!

Jake, it works great after making one correction in line 6 (I changed "Sales" to "Margin").
Thanks for your help!!! :beerchug:

Jacob Hilderbrand
03-26-2005, 12:38 PM
You're Welcome :beerchug:

Take Care