none
If>Then>Else function to hide/show rows RRS feed

  • Question

  • Hello! I asked a question regarding my issue here: https://social.technet.microsoft.com/Forums/en-US/7954f836-0248-46cd-a560-084453a73bfb/excel-move-rows-up-if-blank?forum=excel

    And after a proposal to solve my problem i quickly realized it would not solve my problem. So now I pose my question here instead.

    I want to be able to show and hide entire rows based on if there is the letter X in one cell. Example below:

    Options for spreadsheet:

    Control Cell 1:        X
    Control Cell 2:       
    Control Cell 3:        X


    How it looks when I do it now:

    Control Cell 1 is checked with an X and therefore I exist
    (This is blank, but this space should not exist.)
    Control Cell 3 is checked with an X and therefore I exist


    How I want it to look:

    Control Cell 1 is checked with an X and therefore I exist
    Control Cell 3 is checked with an X and therefore I exist

    And changing the X in the control cells should change the spreadsheet in real-time.

    If you require pictures of the options part and presentable part of my excel file I can provide such in an email.

    I can imagine a code like the one below to work. But I do not know how to make code for Excel.

    IF>
        "B11" = "X"
        THEN>
                 Show  row ("A48")
        ELSE>
                 Hide row ("A48")

    I truly hope you can help me in my struggle.

    Best regards,
    Bishiba


    • Edited by Bishiba Tuesday, March 29, 2016 10:59 AM
    Tuesday, March 29, 2016 10:55 AM

Answers

  • Hi Bishiba,

    From the image you have post above its look like you have try to write the event in sheet manually.

    it will not work.

    you have to take Worksheet_Change event and then you have to just paste the above mentioned code.

    for selecting the Worksheet_Change event please see the image below.

    then it will add the event and look like below.

    then you have to just paste the code which I have again paste below and also make some changes so that you can understand it easily. after pasting the code it look likes below.

    updated code below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B10 As String
    B10 = Worksheets("Sheet1").Cells(10, "B").Value
    Application.ScreenUpdating = False
    
        If (B10) = "x" Then
            Range("A47").EntireRow.Hidden = False
        Else
            Range("A47").EntireRow.Hidden = True
        End If
    End Sub
    

    then you have to just add the values in cell B10. if the value is "x" then the Cell "A47" will display otherwise it will hidden.

    Hope now you understand that what changes should make to run the above code.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 25, 2016 8:58 AM
    Moderator

All replies

  • And changing the X in the control cells should change the spreadsheet in real-time.
    IF>
        "B11" = "X"
        THEN>
                 Show  row ("A48")
        ELSE>
                 Hide row ("A48")

    Copy the code below into the code module of the sheet.

    Andreas.

    Private Sub Worksheet_Change(ByVal Target As Range)
      'Which cell is changed?
      Select Case Target.Address(0, 0)
        Case "B11"
          Range("A48").EntireRow.Hidden = Target <> "X"
      End Select
    End Sub
    

    • Proposed as answer by André Santo Tuesday, March 29, 2016 6:07 PM
    Tuesday, March 29, 2016 2:14 PM
  • Hi, Bishiba

    As you said

    >>How I want it to look:

    Control Cell 1 is checked with an X and therefore I exist
    Control Cell 3 is checked with an X and therefore I exist

    IF>
       
    "B11" = "X"
        THEN
    >
                
    Show  row ("A48")
        ELSE
    >
                
    Hide row ("A48")

    And changing the X in the control cells should change the spreadsheet in real-time. you can try following code.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range
    Set r = Selection
    Application.ScreenUpdating = False
    
        If (r) = "x" Then
            r.EntireRow.Hidden = False
        Else
            r.EntireRow.Hidden = True
        End If
    
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 30, 2016 3:57 AM
    Moderator
  • Hello!

    I have been trying to figure out how to make this work in my sheet, but as of yet I've been unable to make it work. I haven't a clue as to where to input the codes and what values to change. If I could get a quick tutorial on how to input this code into my sheet that would be greatly appreciated!

    Best regards,
    Bishiba

    Wednesday, April 13, 2016 11:03 AM
  • Hi Bishiba

    As you don’t know how to use VBA with Excel. I would like to suggest you to visit the following link.

    Getting Started with VBA in Excel

    First you have to visible the Developer Tab in the Ribbon. Then you have to click on Visual Basic or you can just press Alt + F11 key to start the Visual Basic Editor.

    Then in the worksheet you have to create an event Worksheet_change.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Target
    Application.ScreenUpdating = False
    
        If (r) = "x" Then
            r.EntireRow.Hidden = False
        Else
            r.EntireRow.Hidden = True
        End If
    End Sub
    

    Paste the above mentioned code in that event.

    Then you have to just make an entry in your worksheet.

    If cell contains “X” the row will be visible. If it is empty or some other value it will not be visible.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 7:04 AM
    Moderator
  • Hello!

    I would love to learn a code language. But for something this specific I am assuming I would have to read chapter after chapter just to figure out how to implement one item into my worksheet.

    What I have done is that I have pasted the code into Sheet1 which is in the folder Microsoft Excel Objects within alt-F11. And after looking at the code I am assuming that r is a variable. And that r is both Range and the Target.

    So I've been tinkering a bit, and the code now looks like this: (Only a picture)

    Picture of Code

    I've been trying different combinations of the cellvalues to see what happens. Nothing happens in the sheet though.

    How the sheet with code is supposed to appear: (Only a picture)

    Picture of entire Worksheet

    I've been trying to get A47 to disappear and reappear by changing the X in B10. So if you could refer to how I can make that happen that would be great.

    Thanks so much for any help, I really need this to work.

    Best regards,
    Bishiba


    • Edited by Bishiba Thursday, April 21, 2016 3:07 PM Added links in a proper way
    Thursday, April 21, 2016 3:06 PM
  • Hi Bishiba,

    From the image you have post above its look like you have try to write the event in sheet manually.

    it will not work.

    you have to take Worksheet_Change event and then you have to just paste the above mentioned code.

    for selecting the Worksheet_Change event please see the image below.

    then it will add the event and look like below.

    then you have to just paste the code which I have again paste below and also make some changes so that you can understand it easily. after pasting the code it look likes below.

    updated code below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B10 As String
    B10 = Worksheets("Sheet1").Cells(10, "B").Value
    Application.ScreenUpdating = False
    
        If (B10) = "x" Then
            Range("A47").EntireRow.Hidden = False
        Else
            Range("A47").EntireRow.Hidden = True
        End If
    End Sub
    

    then you have to just add the values in cell B10. if the value is "x" then the Cell "A47" will display otherwise it will hidden.

    Hope now you understand that what changes should make to run the above code.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 25, 2016 8:58 AM
    Moderator
  • Been trying it for about two weeks now, it works perfectly.

    Thanks you so much for your help!

    Best regards,
    Bishiba

    Tuesday, May 10, 2016 9:12 AM