Results 1 to 9 of 9

Automatically hide/unhide rows based on cell value in excel

This is a discussion on Automatically hide/unhide rows based on cell value in excel within the Excel Questions forums, part of the Question Forums category; Hi, I'm new to excel VBA . Need to do the following in a worksheet "Inlet chevron" If cell A3 ...

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    6

    Default Automatically hide/unhide rows based on cell value in excel

    Hi,
    I'm new to excel VBA.
    Need to do the following in a worksheet "Inlet chevron"
    If cell A3 = "Combination", then hide rows 7 to 64 and unhide rows 65 to 94
    If cell A3 ="Single", then hide rows 65 to 94 and unhide rows 7 to 64
    Appreciate if you could type it down so that I could copy it.
    Thanks in Advance.

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,293

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Try this: right click the sheet tab, select View Code and paste in

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "A3" Then
        Select Case Target.Value
            Case "Combination": Rows("7:64").Hidden = True: Rows("65:94").Hidden = False
            Case "Single": Rows("7:64").Hidden = False: Rows("65:94").Hidden = True
        End Select
    End If
    End Sub
    then try changing the value of A3.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    6

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Forgot to mention working on excel 2003.
    Copied and pasted, but not working.

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,293

    Default Re: Automatically hide/unhide rows based on cell value in excel

    It is working here in Excel 2000.

    Macros must be enabled (lowest security setting).

    What changes A3 - you or does it contain a formula?
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Could be a case issue

    The following works if the value in A3 is either one or the other

    Code:
     
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$3" Then
            Rows("7:64").Hidden = (Target.Value = "Combination")
            Rows("65:94").Hidden = (Target.Value = "Single")
        End If
    End Sub
    The 'option compare' statement cancels out case sensitivity. Make sure it appears at the top of any module you use it in.
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  6. #6
    New Member
    Join Date
    Jun 2011
    Posts
    6

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Thanks a lot.
    The latest one sent is working.

  7. #7
    New Member
    Join Date
    Jan 2013
    Posts
    1

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Hi there,

    I'm having a similar problem. I am able to hide a range of rows based the value of a certain cell but I'm having trouble hiding specific rows. If Sheet 1 B47 is blank or n/a then I need a list of rows in sheet 2 to be hidden ie row 10, 25, 40, 88 and 103.

    I've tried this but it gives me an error (my sheet 2 is named Evaluation)

    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$47" Then
    Sheets("Evaluation").Rows("10:10,25:25,40:40,88:88,103:103").EntireRow.Hidden = (Target.Value = "N/A")
    End If
    End Sub

    Also I need the rows to un-hide as soon as the cell B47 is populated with info

    Please help!
    Last edited by Lo_cm; Jan 30th, 2013 at 11:28 AM.

  8. #8
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Re: Automatically hide/unhide rows based on cell value in excel

    Hi could someone help me please on first post....

    I have below code which i copies from this post but it doesnt work exactly how i need.
    I want to hide rows based on drop down box in cell A10
    if i have "5 Metre" selected, only rows 22-23 are hidden. I beleive this is because 6 Metre and 7 Metre are false so rows 24-40 wont hide. I tried & metre at the top but still wont work.
    Anyone able to help with my problem?
    Thanks Heapy


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$10" Then
    Rows("22:40").Hidden = (Target.Value = "5 Metre")
    Rows("24:40").Hidden = (Target.Value = "6 Metre")
    Rows("26:40").Hidden = (Target.Value = "7 Metre")

  9. #9
    New Member mpShock's Avatar
    Join Date
    Jan 2014
    Posts
    6

    Default Re: Automatically hide/unhide rows based on cell value in excel

    I am trying to do this but it seems to not work due to a formula that i have in "K3". how to i get the value that is in K3 (which is the formula =VLOOKUP(TRIM(C3),'OFF Seniority'!A3:AK2501,5,FALSE) )? from what i can tell i need to evaluate it some how.

    thank you for your help in advance


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(False, False) = "K3" Then

    Select Case Target.Value
    Case "W1": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
    Case "W2": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
    Case "W3": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
    Case "W4": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
    Case "W5": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
    Case "O1": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O2": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O3": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O4": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O5": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O6": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O7": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    Case "O8": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
    End Select

    End If
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com