Closed Thread
Results 1 to 9 of 9

Need Help. VBA hide row based on pull down

  1. #1
    Registered User
    Join Date
    07-14-2011
    Location
    na
    MS-Off Ver
    Excel 2010
    Posts
    20

    Need Help. VBA hide row based on pull down

    Hi Y'all. Hope i am in the right spot for some VBA help. Novice here.
    I have an action item/ gantt chart spread sheet. I would like the entire row to hide via a pull down for is the item complete status (yes/ no pull down). I have code that will hide 1 row, and a button to "show all hidden rows" that finction. I need the code to work for more than 1 row - i need it to work whenever the status is changed to complete.
    Here is what I have so far:

    PHP Code: 
    "Private Sub Worksheet_Change(ByVal Target As Range)

        If Not Intersect(Target, Range("
    o6:o6")) Is Nothing Then
            If Range("
    O6").value = "Yes" Then
                Rows("
    6:6").Hidden = True
            Else
                Rows("
    6:6").Hidden = False
            End If
        End If
    End Sub" 
    Also - column "O" is where the pull down is...
    Thanks in advance for your help - and sorry if i didnt post the script correct..
    Kevin

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need Help. VBA hide row based on pull down

    kbotta,

    Welcome to the forum. Give the following a try:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

  3. #3
    Registered User
    Join Date
    07-14-2011
    Location
    na
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Need Help. VBA hide row based on pull down

    Most excellent! Works exactly as I envisoned it!
    Can't thank you enough!!!!
    Kevin

  4. #4
    Registered User
    Join Date
    07-14-2011
    Location
    na
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Need Help. VBA hide row based on pull down

    Ok, 1 more thing. When I change the pull down to "no" (I also have a "blank" identified) it gives me this error "object variable or with block variable not set" Run time 91 error.
    i am sure I just need to identify the other two variables fom the pull down menu. Could you help with the code for that?
    Thanks!
    Kevin

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need Help. VBA hide row based on pull down

    kbotta,

    That's a coding error on my part. It just means that there are no rows to hide if you didn't select Yes, but its trying to hide non-existent rows anyway, which is causing the error. Change this line:
    Please Login or Register  to view this content.


    To be this instead:
    Please Login or Register  to view this content.


    And that should take care of it.
    ~tigeravatar

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need Help. VBA hide row based on pull down

    kbotta,

    Also, because of the error and subsequent break in code running, worksheet events will be disabled. You'll either have to close/re-open the workbook, or use the following, separate macro to re-enable them:
    Please Login or Register  to view this content.

    ~tigeravatar

  7. #7
    Registered User
    Join Date
    07-14-2011
    Location
    na
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Need Help. VBA hide row based on pull down

    Works! Thank you again!

  8. #8
    Registered User
    Join Date
    06-11-2012
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Need Help. VBA hide row based on pull down

    Please Login or Register  to view this content.
    Hi,

    I can't get this macro to work in Excel 2010. If I include the "(ByVal Target As Range)", Excel doesn't seem to recognise this as a macro.
    Also, how do I create a button to toggle show/hide rows?
    Last edited by arlu1201; 06-12-2012 at 02:35 AM. Reason: Code tags.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need Help. VBA hide row based on pull down

    MyronR,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed 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