+ Reply to Thread
Results 1 to 11 of 11

hide & unhide columns on different sheet based on criteria

  1. 07-17-2018, 06:37 AM #1
    esbencito
    esbencito is offline
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    hide & unhide columns on different sheet based on criteria

    Hi all,

    I need a Macro that I can call with a keystroke to toggle between hiding and unhiding columns on a different sheet.

    My "view" sheet has all relevant attribute columns from the "Buy" sheet and clients can click the 'tick box' to keep columns visible or hide them. The Macro should NOT run every time they select or unselect a column, but rather whenever they are on the "Buy" sheet and use the keystroke e.g. "CTRL + SHIFT + 1", which then unhides and hides the columns just as specified on the "view" sheet.

    I have attached a sample workbook to make things more clear...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-18-2018, 11:23 PM #2
    esbencito
    esbencito is offline
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: hide & unhide columns on different sheet based on criteria

    Maybe something like below? I want the code to loop through the range on sheet "view" and whenever it finds "False", hide that column on sheet "Buy".

    e.g.

    cell C6 on sheet "view" contains "False", hence column D would be hidden on sheet "Buy". Basically the second cell in range "B6:E6" on the "view" sheet, refers to the second column within the range "C:F" on the "Buy" sheet. Both ranges will obviously always have the same width.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 07-19-2018, 01:52 AM #3
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,274

    Re: hide & unhide columns on different sheet based on criteria

    Untested (on iPad)

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 07-19-2018, 04:21 AM #4
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: hide & unhide columns on different sheet based on criteria

    hhmmm
    not so sure about this
    whenever they are on the "Buy" sheet and use the keystroke e.g. "CTRL + SHIFT + 1"
    but this code should do what you want

    Please Login or Register  to view this content.
    didnt want use worksheet select but its the only way i could consistently get the same result
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  5. 07-19-2018, 04:32 AM #5
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: hide & unhide columns on different sheet based on criteria

    arr bugger i only got the first half working....ill try again tomorrow
    Reply With QuoteRegister To Reply
  6. 07-19-2018, 04:52 AM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: hide & unhide columns on different sheet based on criteria 

    Please Login or Register  to view this content.
    take 2

    someone could fix it up so there is only 1 array...but i cant think of an easy way to do it at the moment
    so just made two seperate arrays instead
    Last edited by humdingaling; 07-19-2018 at 04:55 AM.
    Reply With QuoteRegister To Reply
  7. 07-19-2018, 05:06 AM #7
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,274

    Re: hide & unhide columns on different sheet based on criteria

    Based on the actual workbook ranges:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 07-19-2018, 06:21 AM #8
    esbencito
    esbencito is offline
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: hide & unhide columns on different sheet based on criteria

    I came up with an adjustment of rorya's initial code. It seems to work, but there's probably a better way to do it!

    @humdingaling Will further try your code tomorrow, but it seems promising!

    Thanks both for your effort so far!! 

    Please Login or Register  to view this content.
    Last edited by esbencito; 07-22-2018 at 10:54 PM.
    Reply With QuoteRegister To Reply
  9. 07-22-2018, 08:18 PM #9
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: hide & unhide columns on different sheet based on criteria

    my method doesnt quite work because it relies on there not being any gaps in headers like yours had
    ie column 90 jumps to 97 on the second lot of headers

    you cant really used the match formula you had in there because it doesnt account for duplicates
    ie, PH = 50 - appears 8 times

    anyways i amended the file slightly in order to work
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 07-25-2018, 02:04 AM #10
    esbencito
    esbencito is offline
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: hide & unhide columns on different sheet based on criteria

    Sorry, been without Excel for a few days!

    Thanks!! I'm still trying to get my head around how your code works. Don't understand much about LBound and UBound code (yet)

    However, it seems to do what it is supposed to do!
    Reply With QuoteRegister To Reply
  11. 07-25-2018, 02:31 AM #11
    esbencito
    esbencito is offline
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: hide & unhide columns on different sheet based on criteria

    Quote Originally Posted by humdingaling View Post
    hhmmm
    not so sure about this

    whenever they are on the "Buy" sheet and use the keystroke e.g. "CTRL + SHIFT + 1"
    Btw... I did some search and just found this:

    Please Login or Register  to view this content.
    This code in the ThisWorkbook module applies the CTRL + SHIFT + 1 shortcut to a specific macro
    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. Way to hide or unhide sheets based on selection criteria
    By jshrader in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-21-2016, 07:28 PM
  2. hide and unhide columns based on cell value
    By darijokesar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-29-2014, 03:51 AM
  3. Hide/Unhide row based on given criteria w. no Trigger butn
    By m1zz13 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-18-2013, 06:31 AM
  4. Hide/Unhide Rows based on Criteria
    By Phillycheese5 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2010, 02:11 PM
  5. Hide & Unhide columns based on a cell value
    By DaveNUFC in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-21-2010, 09:40 AM
  6. [SOLVED] Hide or Unhide certain columns based on a cell value
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 01:04 PM
  7. [SOLVED] Macro to hide and unhide based on criteria
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 06-05-2006, 03:10 PM

Tags for this Thread

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