Conditional hiding of rows with multiple Ifs

How does one write this?

1. In a worksheet named "Control Panel" the user types in the descriptions of market segments in B2:B21
2. Using the COUNTA function, cell D1 in Sheet "Control Panel" returns the number of populated rows (some rows are likely to be blank)
3. 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!

Try something like this:

Code:

---

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

Code:

---

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

---

In a regular Module, I presume...

Thanks!

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:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

---

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?

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?

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"

Try this macro. Place the code in the "Contol Panel" sheet module.

Code:

---

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

---

I will, thanks!

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

You're Welcome :beerchug:

Take Care