+ Reply to Thread
Results 1 to 28 of 28

Auto Hiding rows based on range/data present or not present.

  1. #1
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Wink Auto Hiding rows based on range/data present or not present.

    I need a macro to run with a button.

    If I have a range of say A1:T8

    Rows 1 and 2 are heading rows and rows 3-8 have data. If row 3 is empty of data then rows 1-8 are hidden. If row 3 has data then check each row after it and hide rows with no data.

    Then I need a simple macro for another button to unhide all hidden rows.

    Thank you in advance for the help.

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Auto Hiding rows based on range/data present or not present.

    Hi

    Try this:

    HIDEUNHIDEROWS.xlsm
    Click *, if my suggestion helps you. Have a good day!!

  3. #3
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    I checked this code. Its a good start. I noticed the range when checking for empty rows isnt checking the entire row. A range from A to T is needed when checking for content.

  4. #4
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Here is the code for for any willing to have a look at it: The highlighted section is where the range is needed.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    bump :P I'd really like to get this done today so I can finish my project. If any one can look I am very appreciative.

  6. #6
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Tried this but it doesn't seem to work:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    This is what I'd try (untested, because I refuse to set up workbooks for testing any more..lol )
    Please Login or Register  to view this content.
    the red areas are the changes
    That's just a suggestion, my VBA skills are low, and there is no guarantee on it (well there is 1 guarantee, it's guaranteed to be %100 effective until you use it...lol)
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  8. #8
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    ok cool. the first chsnge I tried that too but I didn't know I'd need to make changes on the second red line. Thanks dredwolf.

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    Hey, if it works, I am pleased!
    Was a shot in the dark, based on the "old school" programming skills I still remember...lol
    And you are welcome

  10. #10
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Meh...that first red section dubugs as mismatch error.

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    Well, next step is give me something to work with, I won't create my own test-bed for this, because I have no idea of the set-up of the data

  12. #12
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    ok will work on it asap

  13. #13
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Check this out. Its working now. See where I changed the : to ,

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Thanks to both of you for the help.

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    Cool !
    Glad you could make it work!
    (I did warn you about the warranty though....lol )

  16. #16
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    uhh well I spoke too soon. I took it step further. But its still closing rows if column A is empty. So Im loading the latest version of my file with the macro in it so you can see it.

    edit: its the calender page. I want to make the calender concise for viewing/printing purposes.





    Multi_Family_Assistant.xlsm

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    You have went beyond my skills...and the upload does not give testing options,all the cells from a3- to the calander start are blank...what do we test against?

  18. #18
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Well the first header is the week day. So we always want it to display.

    The next two rows are headers for the community/tech etc. So if there are entries in the cells below the community/tech header then we want the rows visible with data. If there are no entries then the community/tech headers are hidden along with the data cells for those headers.

    If you change the last tuesday stop (doing so on the admin page) to monday you can see the row will not hide. but it is hiding that row when the data is on any day but monday. So thats the test. If column a below the headers is empty and yet there is data in the row then the row should not hide. Maybe you know another member on the forum who can help?

  19. #19
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto Hiding rows based on range/data present or not present.

    This one is beyond me..sorry

  20. #20
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    ohes nhoes lol ok ty any way.

  21. #21
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Auto Hiding rows based on range/data present or not present.

    Use this function to check each row you want. If it returns 0 hide that row, if not unhide it.

    Please Login or Register  to view this content.
    Last edited by Yraen; 02-10-2013 at 01:05 AM. Reason: Found my fat finger error

  22. #22
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    lol "fat finger error." I know what ya mean. This looks hopeful. I will work with this code later today. Is it already set to hide the rows returning zero? If not then Im already going to be at a loss trying to implement this. I have no skills in any form of Excel coding.

  23. #23
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Im lost. I don't know where to place the function or how to make it execute. Please advise.

  24. #24
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Auto Hiding rows based on range/data present or not present.

    That function will just return the number of the last column of a row that has a value or a 0 if there is no value in any column. You still need to write the loop to search each row to see if it is to be hidden or not. I'll write the HideRows function you've currently got attached to the Update Calendar Button if you want but I'll need some more information to make sure I understand what you want.

    Quote Originally Posted by raze View Post
    Rows 1 and 2 are heading rows and rows 3-8 have data. If row 3 is empty of data then rows 1-8 are hidden. If row 3 has data then check each row after it and hide rows with no data.

    Then I need a simple macro for another button to unhide all hidden rows.
    In the sample you uploaded, row 1 has a value of 11 and rows 2 thru 5 are blank, row 6 contains the weekdays, rows 7 and 8 contain the header information (Albert, Barton, etc) about what you'll be displaying in rows 9 thru 17 (and so on down the sheet). Going by what you put in the first post if row 3 is blank hide rows 3 thru 8? Or did you mean check the first row of each header section and hide rows without values for each header section? Would you want that header section hidden also? Rows 1 thru 5 are throwing me off, I'm not sure what data is supposed to be shown there or if they are just for spacing to keep the button from being inside the calendar.

    Also, would you want the one button to make all rows showing data visible at the same time or a button to just show all rows regardless of data visible?

  25. #25
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    Ignore the first post. Take the last attachment and you can see the code there. First post was just an example where the attachment has a live version. The idea is that if there is any entry on the first row after the headers then that row along with headers will remain visible. If there is no data in the first row after the header then the header and data rows below will be hidden with the button.

    The current check on the update button is checking only the first row but when it goes to the next rows its only checking column a. Thats the problem right now. If there is any entry else where the entry is ignored and the row becomes hidden. Also please don't take any action with the data in A1. The formula in the calender requires that number.


    edit: to add more notes to clarify.
    Last edited by raze; 02-10-2013 at 09:38 AM.

  26. #26
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Auto Hiding rows based on range/data present or not present.

    Replace your current HideRows function with this:
    Please Login or Register  to view this content.
    I like to use the status bar to show information about what is currently happening to the user. You can remove it if you want.
    Last edited by Yraen; 02-10-2013 at 10:28 AM. Reason: Added a comment I missed

  27. #27
    Registered User
    Join Date
    12-29-2012
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Auto Hiding rows based on range/data present or not present.

    This is doing exactly what I need. Thank you very much

  28. #28
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Auto Hiding rows based on range/data present or not present.

    You're welcome.

+ 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