+ Reply to Thread
Results 1 to 4 of 4

Comprehensive row-hiding in VBA

  1. 08-08-2016, 10:26 AM #1
    -Sindre
    -Sindre is offline
    Registered User
    Join Date
    08-08-2016
    Location
    Oslo
    MS-Off Ver
    2013
    Posts
    7

    Comprehensive row-hiding in VBA

    First of all, I'm totally new to VBA and I'm learning as I go. I'm working on an excel-sheet where I want to hide/unhide rows depending on some choices the user do as input in the beginning.

    So for instance if I have four choices in the beginning (1 and 0), these could be:
    1
    0
    1
    0
    IF these values are chosen I want to hide row 42, 56-60 and 70.

    Or:
    0
    1
    1
    1
    IF these values are chosen I want to hide row 28, 31-34 and 72-74.

    And so on, for all possibly combinations of the 4-input-cells (2*2*2*2=16).

    I figured one way was to choose which cells to show and which to hide for every single combination. Then I get something like the code shown at the bottom (this is for one choice 0 or 1 and one choice 0, 1 or 2). This doesn't work, any clues why? Also, is there another way to do this? I got around 400 possible combinations so having to do this for every one is quite a hassle


    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
            Application.ScreenUpdating = False
            If Target.Column = 4 And Target.Row = 4 And Target.Value = "0" Then
                If Target.Column = 4 And Target.Row = 5 And Target.Value = "0" Then
                Rows    ("1:34").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("35:35").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("36:65").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("66:82").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("83:95").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("96:96").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("97:101").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("102:104").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("105:244").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("245:253").Select
                Selection    .EntireRow.Hidden = True
                ElseIf Target.Column = 4 And Target.Row = 5 And Target.Value = "1" Then
                Rows    ("1:34").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("35:35").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("36:75").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("76:82").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("83:95").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("96:96").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("97:101").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("102:104").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("105:246").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("247:253").Select
                Selection    .EntireRow.Hidden = True
                ElseIf Target.Column = 4 And Target.Row = 5 And Target.Value = "2" Then
                Rows    ("1:34").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("35:35").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("36:101").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("102:104").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("105:253").Select
                Selection    .EntireRow.Hidden = False
            End     If
        ElseIf     Target.Column = 4 And Target.Row = 4 And Target.Value = "1" Then
                If Target.Column = 4 And Target.Row = 5 And Target.Value = "0" Then
                Rows    ("1:65").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("66:82").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("83:95").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("96:96").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("97:100").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("101:101").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("102:244").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("245:253").Select
                Selection    .EntireRow.Hidden = True
                ElseIf Target.Column = 4 And Target.Row = 5 And Target.Value = "1" Then
                Rows    ("1:75").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("76:82").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("83:95").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("96:96").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("97:100").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("101:101").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("102:246").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("247:253").Select
                Selection    .EntireRow.Hidden = True
                ElseIf Target.Column = 4 And Target.Row = 5 And Target.Value = "2" Then
                Rows    ("1:100").Select
                Selection    .EntireRow.Hidden = False
                Rows    ("101:101").Select
                Selection    .EntireRow.Hidden = True
                Rows    ("102:253").Select
                Selection    .EntireRow.Hidden = False
            End     If
            End If
            Application.ScreenUpdating = True
    End Sub 
    Reply With QuoteRegister To Reply
  2. 08-08-2016, 11:28 AM #2
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Comprehensive row-hiding in VBA

    1.) The logic of this is flawed
    Please Login or Register  to view this content.
    The first IF tests if the target.row = 4.
    The 2nd IF, nested in the TRUE block of the 1st IF, tests if the Target.Row = 5.
    The 2nd IF will never be true because if it passed the 1st IF test, you know the Target.Row = 4


    2.) This...
    Please Login or Register  to view this content.
    ...can be reduced to this...
    Please Login or Register  to view this content.

    3.) If you have any repeating patterns to your data layout, you may be able to calculate an offset based on one of the inputs and use the same code for different areas on your sheet that share a layout pattern.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  3. 08-09-2016, 04:26 AM #3
    -Sindre
    -Sindre is offline
    Registered User
    Join Date
    08-08-2016
    Location
    Oslo
    MS-Off Ver
    2013
    Posts
    7

    Re: Comprehensive row-hiding in VBA

    Thank you very much for a good answer!

    I've fixed the code and it works now if I choose one and one. However, I still need to show/hide rows depending on two (or more) inputs.
    Example:
    If both A4=1 and B4=1
    I want to hide row 6-10
    If A4=1 and B4=0
    I want to hide row 6-10
    If A4=0 and B4=1
    I want to show row 6 and 8-10, and hide row 7
    If A4=0 and B4=0
    I want to show row 6-10

    Is there a way to check the input of two (or more) cells with one IF?
    Reply With QuoteRegister To Reply
  4. 08-09-2016, 07:42 AM #4
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Comprehensive row-hiding in VBA

    Quote Originally Posted by -Sindre View Post
    Thank you very much for a good answer!

    I've fixed the code and it works now if I choose one and one. However, I still need to show/hide rows depending on two (or more) inputs.
    Example:
    If both A4=1 and B4=1
    I want to hide row 6-10
    If A4=1 and B4=0
    I want to hide row 6-10
    If A4=0 and B4=1
    I want to show row 6 and 8-10, and hide row 7
    If A4=0 and B4=0
    I want to show row 6-10

    Is there a way to check the input of two (or more) cells with one IF?
    You are already testing two or more cells with the AND operator, but I don't think that's what you need. I think you want to test one cell, then with a nested IF, test the other cells e.g.

    Please Login or Register  to view this content.

    Another way is to concatenate the values of each test cell and evaluate the concatenated result.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Comprehensive Leave Spreadsheet
    By Serenitty in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2015, 03:34 PM
  2. Help creating comprehensive bargraph
    By marcushalberstam in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-19-2012, 11:47 PM
  3. Excel 2007 : More comprehensive Averageif function
    By derpotheman in forum Excel General
    Replies: 5
    Last Post: 02-04-2011, 01:38 PM
  4. Comprehensive Duplicate Formula
    By sethcnorris in forum Excel General
    Replies: 1
    Last Post: 10-10-2008, 04:21 PM
  5. Comprehensive gathered from various worksheets?
    By negaither in forum Excel General
    Replies: 7
    Last Post: 03-05-2007, 04:43 PM
  6. Looking for some well-thought out, comprehensive advice
    By splodgecat in forum Excel General
    Replies: 3
    Last Post: 01-18-2007, 12:05 PM
  7. [SOLVED] Most comprehensive list of shortcuts!
    By alana31 in forum Excel General
    Replies: 0
    Last Post: 10-03-2005, 12:05 PM

Bookmarks

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


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1