Consulting

Results 1 to 10 of 10

Thread: Conditional hiding of rows with multiple Ifs

  1. 03-25-2005, 09:21 PM #1
    K. Georgiadis
    K. Georgiadis is offline
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    368
    Location

    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!
    Reply With Quote Reply With Quote
  2. 03-25-2005, 09:59 PM #2
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master         VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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



    Reply With Quote Reply With Quote
  3. 03-26-2005, 07:02 AM #3
    K. Georgiadis
    K. Georgiadis is offline
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    368
    Location
    In a regular Module, I presume...

    Thanks!
    Reply With Quote Reply With Quote
  4. 03-26-2005, 07:44 AM #4
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    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
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
  5. 03-26-2005, 07:48 AM #5
    K. Georgiadis
    K. Georgiadis is offline
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    368
    Location
    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?
    Reply With Quote Reply With Quote
  6. 03-26-2005, 07:51 AM #6
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    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?
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
  7. 03-26-2005, 08:15 AM #7
    K. Georgiadis
    K. Georgiadis is offline
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    368
    Location
    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"
    Reply With Quote Reply With Quote
  8. 03-26-2005, 10:08 AM #8
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master     VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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



    Reply With Quote Reply With Quote
  9. 03-26-2005, 12:00 PM #9
    K. Georgiadis
    K. Georgiadis is offline
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    368
    Location
    I will, thanks!

    Jake, it works great after making one correction in line 6 (I changed "Sales" to "Margin").
    Thanks for your help!!!
    Reply With Quote Reply With Quote
  10. 03-26-2005, 12:38 PM #10
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master     VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care



    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules