+ Reply to Thread
Results 1 to 19 of 19

Auto Hide/Unhide rows in Excel based on formula result in a column

  1. #1
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Auto Hide/Unhide rows in Excel based on formula result in a column

    Hi,

    I am new to VBA/Macros and am looking for some help please.

    I have a spreadsheet where I would like the rows to hide/unhide automatically based on a value in a column (either 0 or 1). The value is driven by a formula. I want rows with 1 value to display (unhide) and rows with 0 value to hide. I also want to be able to apply the macro to a range of rows but for it to ignore (ie keep displayed) rows where the column value is blank.

    Attached is a simple example. Can anyone help me with the code please?

    Thanks
    Kim
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Thanks patel45.

    How do I get this macro to autorun each time a response in column B (in the "Example A.xls file attached above) is changed?

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Thanks again patel45.

    I added the above to the code and it is responding to a response in column B.

    The only problem I am having now is that it only responds to a change in cell B2 and only the rows from 3:5 hide/unhide - these being the rows on which the formula in column D references cell B2. I need rows 7:9 to hide/unhide based on the values in column D (the formula in column D for these rows in based on cell B6). See attached revised file.

    Any suggestions on how to modify the code to overcome this?
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    check the formulas (=IF($B$2="Yes";1;0)), they are all wrong, you have to remove $

  7. #7
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Hi patel45,

    I'm not sure I understand your comment, but I have removed the $ from the formulas (revised file attached) and am still having a problem with rows 7-9.

    The formula in column D is designed to determine if the row should be hidden (value = 0) or unhidden (value = 1). In the simple example I have attached, this is based on whether the answer to a question is "Yes" or "No". So in the example attached, if the answer in cell B6 is "Yes", then I want rows 7-9 to display. But if it is "No", then I want them hidden. The macro seems to work to hide them but if I change the answer from "No" to "Yes", the macro does not work to unhide them.

    Thanks so much for your help to date. If you have any further suggestions on how to overcome this final hurdle I would greatly appreciate it.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    You put the response Yes or Not only in Question1, Question2 ... or also in Question 1A, 1B ..... ?

  9. #9
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    If the answer to Question 1 is Yes, then a response is required in 1A, 1B and 1C (but these sub questions do not affect the formula in column D). If the answer to Question 1 is No, then a response is not required for 1A, 1B and 1C - and hence the reason I want to hide those rows.

  10. #10
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Ok, I was wrong when I suggested eliminate $, but it's not a problem.
    if you change the answer from "No" to "Yes", the macro does not work to unhide them because does not find the rows with 1, why do you make search for 1,0 and not for Yes, No ?

  11. #11
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    I cannot think of a reason why I would need to keep the formula to returning a result of 1, 0.

    I could probably change my formulas to return Yes or No.

    Would that help to get the macro achieving my desired outcome?

  12. #12
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    When the last rows are hidden this line
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    counts only visible rows, then the macro can not unhide.
    Do you know in advance the number of questions? Can we avoid calculating rows number ?

  13. #13
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    I am going to be using it on a checklist which will have a lot of questions 200+ and I expect will be regularly updated/modified (questions added/deleted) so the number of questions and rows will fluctuate over time.

  14. #14
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Can You have a cell with Number of questions always updated ?
    Can you have the last question as dummy question ? not to be compiled ?
    Can You have a last row without questions ?
    We need a last row always present
    Last edited by patel45; 08-12-2012 at 01:50 AM.

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,097

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Hi, patel45,

    how about unhiding the range before running the macro:

    Please Login or Register  to view this content.
    We need a last row always present
    Please Login or Register  to view this content.
    will find the last used row in column A whether visible or not.

    Ciao,
    Holger

  16. #16
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Ok, problem is solved, many thanks HaHoBe, we can change so
    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Ok, problem is solved, many thanks HaHoBe, we can change so
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-11-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    I think I have managed to find a macro that works.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B10")) Is Nothing Then Call m
    End Sub

    Sub m()
    Dim cell As Range
    For Each cell In Range("D2:D10")
    If UCase(cell.Value) = 0 Then
    cell.EntireRow.Hidden = True
    Else
    If UCase(cell.Value) = 1 Then
    cell.EntireRow.Hidden = False
    End If
    End If
    Next

    End Sub


    See attached spreadsheet.

    Thanks so much for all your help. Really appreciate it.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Auto Hide/Unhide rows in Excel based on formula result in a column

    Do you remeber ?
    Quote Originally Posted by crozierk View Post
    I am going to be using it on a checklist which will have a lot of questions 200+ and I expect will be regularly updated/modified (questions added/deleted) so the number of questions and rows will fluctuate over time.
    in the last attached file you have
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B10")) Is Nothing Then Call m
    End Sub

    Sub m()
    Dim cell As Range
    For Each cell In Range("D2:D10")
    If UCase(cell.Value) = 0 Then
    cell.EntireRow.Hidden = True
    Else
    If UCase(cell.Value) = 1 Then
    cell.EntireRow.Hidden = False
    End If
    End If
    Next

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1