+ Reply to Thread
Results 1 to 5 of 5

Macro to hide empty rows

  1. #1
    Registered User
    Join Date
    11-03-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    32

    Macro to hide empty rows

    Hi
    I have a macro on a sheet to hide all empty rows based on the values of cells A55 to A215. The value comes from a droplist that is filled by the users. If the value 1 appears, the row is visible, if nothing appears, the row should hide automatically.

    The macro is working if I change the value manually in column A ("1" or empty) but the fact that the value comes from a Vlookup formula linked to a drop list (H29:H36) caused some problems... So according to the selection in the drop list, the value changes from empty to "1" and this must decide if the row is hide or not. The problem is when I change the value from the droplist, nothing happen. Do you think you could find out why?

    Thanks,

    Please Login or Register  to view this content.
    Dropbox link:
    https://dl.dropbox.com/u/93601544/TE...WS%20EXTR.xlsm
    Last edited by nomis6565; 11-24-2012 at 04:00 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,242

    Re: Macro to hide empty rows

    The Worksheet_Change event procedure is triggered when the user (not a formula) makes a change to the worksheet. The Target within the procedure will be the cell that the user changed and not the cells that changed from the formulas.

    So this line...
    If Not Intersect(Target, Range("A55:A255")) Is Nothing Then
    ...is testing if the Target is one of the formula cells and Target is the cell that the user changed.

    The fix is to change A55:A255 to the cells that the user changes e.g.
    If Not Intersect(Target, Range("H29:H36")) Is Nothing Then

  3. #3
    Registered User
    Join Date
    11-03-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Macro to hide empty rows

    Superbe!, it works perfectly. Would it be possible add a command in the macro to make it work in 2 ways. I mean that for now, the macro works only in one way, if I change a value from "1" to "0" it will hide the row, but if i make another change that switch the "0" to "1" it should unhide the row containing a 1". I'm sure you can make it work!

    Thanks
    Simon

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,242

    Re: Macro to hide empty rows

    Quote Originally Posted by nomis6565 View Post
    Superbe!, it works perfectly. Would it be possible add a command in the macro to make it work in 2 ways. I mean that for now, the macro works only in one way, if I change a value from "1" to "0" it will hide the row, but if i make another change that switch the "0" to "1" it should unhide the row containing a 1". I'm sure you can make it work!

    Thanks
    Simon
    Try this...
    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 11-23-2012 at 11:57 PM.

  5. #5
    Registered User
    Join Date
    11-03-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Macro to hide empty rows

    I knew you would do it! It's just perfect. Thanks so much, It allowed me to terminate the most complex project I worked on Excel with VBA.

    Ragards,
    Simon

+ 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