+ Reply to Thread
Results 1 to 18 of 18

Hide and unhide rows in multiple sheets

  1. 07-02-2024, 06:00 AM #1
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Hide and unhide rows in multiple sheets

    Among other Tabs, my Live Workbook contains 8 x Tabs. – Sheet01, Sheet02, Sheet03, Sheet04, Sheet05, Sheet06, Sheet07, Sheet08.
    Data in all tabs includes names in many rows with additional rows containing ZZZ in rows where names are not yet used.

    I am trying to hide and unhide the rows containing ZZZ in all tabs using VBA code in Sheet01 and using that Code to hide / unhide all other sheets.

    The attached WorkBook is an extract

    The names are the same in all tabs and specific information is added in each Sheet.

    The below code does not work correctly. I get an error as per below

    Hope someone can assist please
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-02-2024, 06:32 AM #2
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Perhps...
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by Sintek; 07-02-2024 at 06:40 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  3. 07-02-2024, 08:45 PM #3
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Thankyou Sintek

    When I try to run Hide or Unhide, I get error window as per attached

    and all ZZZ rows are blank - ReadMe-2

    Could you please amend the code in the attached Hide_Unhide ZZZ_A so it works and attach to your reply
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-02-2024, 09:25 PM #4
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,719

    Re: Hide and unhide rows in multiple sheets 

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by jolivanes; 07-02-2024 at 10:00 PM.
    The inherent weakness of the liberal society: a too rosy view of humanity.
    Reply With QuoteRegister To Reply
  5. 07-02-2024, 10:25 PM #5
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Hide and unhide rows in multiple sheets

    VisionSmart,

    This will toggle Hide/Unhide.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-03-2024, 12:34 AM #6
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Thankyou for responses so far.
    Now I would like to protect each Sheet with different password. and I would tick the box adjacent to Format Rows?
    Assistance please.
    Reply With QuoteRegister To Reply
  7. 07-03-2024, 06:22 AM #7
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Thank you Sintek
    When I try to run Hide or Unhide, I get error window as per attached
    This error is due to two modules with same name...
    Please Login or Register  to view this content.
    I assumed you would change it to Hide & UnHide
    Seem you have enough options now...filtering too...
    Now I would like to protect each Sheet with different password
    Store the passwords in an array and during loop...protect...

    contains 8 x Tabs. – Sheet01, Sheet02, Sheet03, Sheet04, Sheet05, Sheet06, Sheet07, Sheet08 
    Please Login or Register  to view this content.
    Last edited by Sintek; 07-03-2024 at 06:43 AM.
    Reply With QuoteRegister To Reply
  8. 07-03-2024, 12:19 PM #8
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,719

    Re: Hide and unhide rows in multiple sheets

    Personally I would say that the protection part should be handled in a new post.
    People searching the web for a solution for that problem will never end up here because the original header is about hiding/unhiding.
    They will miss out on your elegant solution Sintek.
    Reply With QuoteRegister To Reply
  9. 07-03-2024, 07:06 PM #9
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Thankyou again Sintek.
    I believe I have created separate modules OK
    Updates Workbook as attached - Passwords for testing Sheets are 111,222,333,444,555 and so on. Data areas in each sheet are unlocked via format process.
    I don't have enough knowledge of VBA to create the correct code as per your last suggestion
    Could you please insert the correct code in the WB and attach to your response.
    Attached Files Attached Files
    Last edited by VisionSmart; 07-03-2024 at 07:08 PM. Reason: spell check
    Reply With QuoteRegister To Reply
  10. 07-04-2024, 12:41 AM #10
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Seems you have been consistent in your data layout...
    i.e. "zzz" from a specific area to end of data...
    This for both options then...Not discounting all above options also available...
    Please Login or Register  to view this content.
    Last edited by Sintek; 07-04-2024 at 12:44 AM.
    Reply With QuoteRegister To Reply
  11. 07-04-2024, 01:59 AM #11
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Sintek

    I can use your code to protect - see module 1 in attached WB
    But how do I unprotect? - See module 2 does not work
    Can you please complete the correct code in the WB and attach to your return response.
    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 07-04-2024, 02:27 AM #12
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Are you not wanting this...
    Loop sheets 1 to 8
    unprotect
    hide zzz rows
    protect

    and then after

    Loop sheets 1 to 8
    unprotect
    unhide all rows
    protect

    If not then explain your logic...In Detail...
    Last edited by Sintek; 07-04-2024 at 02:39 AM.
    Reply With QuoteRegister To Reply
  13. 07-04-2024, 02:54 AM #13
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Hello again Sintek
    Here goes on my explanation – (hopefully it tells the story)

    My live workbook contains 400 rows – same in all sheets- with different information in other columns and different password for each sheet.
    Sometimes, not all the rows in each sheet contain a name; So when not in use, the ZZZ signifies that row is available to populate with a name.
    Then, by hiding ZZZ rows, any reports or prints are limited to the rows containing names only.
    And when a sort is run, the ZZZ rows all go to the bottom whether hide or unhide.

    I don’t understand the VBA language very well, but in my interpretation, I would envisage that Hide would
    go to all sheets 01 thru 08, then find ZZZ rows in Column A, then hide the ZZZ rows.
    Unhide would reverse the process so all the ZZZ rows become visible.
    Passwords would always remain to protect data and accidental errors by user.
    Hope this helps
    Reply With QuoteRegister To Reply
  14. 07-04-2024, 03:00 AM #14
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    I would envisage that Hide would
    go to all sheets 01 thru 08, then find ZZZ rows in Column A, then hide the ZZZ rows.
    Unhide would reverse the process so all the ZZZ rows become visible.
    Yes to above...However, you have not explained your logic...
    So...are you wanting two individual macros...

    Hide...
    Loops all sheets
    Unprotects
    Hides zzz rows
    Protects

    Unhide...
    Loops all sheets
    Unprotects
    Unhides all hidden rows
    Protects

    Or if not...What must EACH macro do...
    Last edited by Sintek; 07-04-2024 at 03:02 AM.
    Reply With QuoteRegister To Reply
  15. 07-04-2024, 03:03 AM #15
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    Two individual Macros would be fine please.
    One for Hide, and the other for Unhide, both handle protected sheets
    Reply With QuoteRegister To Reply
  16. 07-04-2024, 03:06 AM #16
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Oh my gosh...That is what Post 10 does...One macro for both...
    Hence...Sub HidePlusUnHide()

    First time when pressed...
    Loops all sheets...Unprotects...Hides zzz rows...Protects...
    Next time when pressed
    Loops all sheets...Unprotects...Unhides all rows...Protects...

    Want them separated...Here you go...
    Please Login or Register  to view this content.
    Last edited by Sintek; 07-05-2024 at 04:33 AM.
    Reply With QuoteRegister To Reply
  17. 07-04-2024, 03:18 AM #17
    VisionSmart
    VisionSmart is offline
    Forum Contributor
    Join Date
    04-22-2017
    Location
    AU
    MS-Off Ver
    Microsoft 365
    Posts
    304

    Re: Hide and unhide rows in multiple sheets

    My mistake Sintek. I did not read the code correctly.
    I will set up on my live version and advise in the next day or so.
    Thank you again.
    Reply With QuoteRegister To Reply
  18. 07-04-2024, 03:21 AM #18
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Hide and unhide rows in multiple sheets

    Is that you want though...You still have not explained your logic...of what each macro must do...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] hide and unhide rows in multiple protected sheets
    By VisionSmart in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 07-14-2022, 02:32 AM
  2. hide and unhide rows in multiple protected sheets
    By VisionSmart in forum Office 365
    Replies: 0
    Last Post: 07-03-2022, 02:48 AM
  3. Hide or unhide rows based on value across multiple sheets
    By Casper99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2016, 08:23 PM
  4. HIDE/UNHIDE Rows based on cell value in multiple sheets.
    By yatin1964 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2015, 12:29 AM
  5. [SOLVED] VBA to hide/unhide rows & columns on multiple sheets
    By Adam23 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-14-2015, 07:06 PM
  6. [SOLVED] Hide and Unhide rows on multiple sheets based on a validation list on a single sheet
    By bbarth in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2015, 02:10 PM
  7. Hide/Unhide rows on multiple sheets when dropdown selected from sheet 1
    By tammhelp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-02-2012, 10:33 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1