|
Macro to Hide/Unhide various columns
I've tried modifying the code below to hide/unhide the columns below, but
can't get this to work. It either hide/unhides columns not specified below or it will only run the first line. I don't really know VBA, but am just trying to record macros and modify them. Please help if you have any ideas. Thanks! Sub View_Onscreen3() ' View_Onscreen Macro Columns("E:E").Hidden = True Columns("S:V").Hidden = True Columns("X:Z").Hidden = True Columns("AB:AB").Hidden = True Columns("AI:AI").Hidden = True Range("Q16").Select End Sub Sub View_Print3() ' View_Onscreen Macro Columns("E:E").Hidden = False Columns("S:V").Hidden = False Columns("X:Z").Hidden = False Columns("AB:AB").Hidden = False Columns("AI:AI").Hidden = False Range("Q16").Select End Sub |
Macro to Hide/Unhide various columns
How about using just one macro to do both. If hidden...make visible and if
visible hide Sub View_Onscreen3() Columns("E:E").Hidden = Not Columns("E:E").Hidden Columns("S:V").Hidden = Not Columns("S:V").Hidden Columns("X:Z").Hidden = Not Columns("X:Z").Hidden Columns("AB:AB").Hidden = Not Columns("AB:AB").Hidden Columns("AI:AI").Hidden = Not Columns("AI:AI").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Kevin K" wrote: I've tried modifying the code below to hide/unhide the columns below, but can't get this to work. It either hide/unhides columns not specified below or it will only run the first line. I don't really know VBA, but am just trying to record macros and modify them. Please help if you have any ideas. Thanks! Sub View_Onscreen3() ' View_Onscreen Macro Columns("E:E").Hidden = True Columns("S:V").Hidden = True Columns("X:Z").Hidden = True Columns("AB:AB").Hidden = True Columns("AI:AI").Hidden = True Range("Q16").Select End Sub Sub View_Print3() ' View_Onscreen Macro Columns("E:E").Hidden = False Columns("S:V").Hidden = False Columns("X:Z").Hidden = False Columns("AB:AB").Hidden = False Columns("AI:AI").Hidden = False Range("Q16").Select End Sub |
Macro to Hide/Unhide various columns
and to further reduce the code you can use the below one liner...
Sub View_Onscreen3() Range("E:E,S:V,X:Z,AB:AB,AI:AI").EntireColumn.Hidd en = _ Not Columns("E:E").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: How about using just one macro to do both. If hidden...make visible and if visible hide Sub View_Onscreen3() Columns("E:E").Hidden = Not Columns("E:E").Hidden Columns("S:V").Hidden = Not Columns("S:V").Hidden Columns("X:Z").Hidden = Not Columns("X:Z").Hidden Columns("AB:AB").Hidden = Not Columns("AB:AB").Hidden Columns("AI:AI").Hidden = Not Columns("AI:AI").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Kevin K" wrote: I've tried modifying the code below to hide/unhide the columns below, but can't get this to work. It either hide/unhides columns not specified below or it will only run the first line. I don't really know VBA, but am just trying to record macros and modify them. Please help if you have any ideas. Thanks! Sub View_Onscreen3() ' View_Onscreen Macro Columns("E:E").Hidden = True Columns("S:V").Hidden = True Columns("X:Z").Hidden = True Columns("AB:AB").Hidden = True Columns("AI:AI").Hidden = True Range("Q16").Select End Sub Sub View_Print3() ' View_Onscreen Macro Columns("E:E").Hidden = False Columns("S:V").Hidden = False Columns("X:Z").Hidden = False Columns("AB:AB").Hidden = False Columns("AI:AI").Hidden = False Range("Q16").Select End Sub |
Macro to Hide/Unhide various columns
That seems to work. Thank you Jacob!
"Jacob Skaria" wrote: and to further reduce the code you can use the below one liner... Sub View_Onscreen3() Range("E:E,S:V,X:Z,AB:AB,AI:AI").EntireColumn.Hidd en = _ Not Columns("E:E").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: How about using just one macro to do both. If hidden...make visible and if visible hide Sub View_Onscreen3() Columns("E:E").Hidden = Not Columns("E:E").Hidden Columns("S:V").Hidden = Not Columns("S:V").Hidden Columns("X:Z").Hidden = Not Columns("X:Z").Hidden Columns("AB:AB").Hidden = Not Columns("AB:AB").Hidden Columns("AI:AI").Hidden = Not Columns("AI:AI").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Kevin K" wrote: I've tried modifying the code below to hide/unhide the columns below, but can't get this to work. It either hide/unhides columns not specified below or it will only run the first line. I don't really know VBA, but am just trying to record macros and modify them. Please help if you have any ideas. Thanks! Sub View_Onscreen3() ' View_Onscreen Macro Columns("E:E").Hidden = True Columns("S:V").Hidden = True Columns("X:Z").Hidden = True Columns("AB:AB").Hidden = True Columns("AI:AI").Hidden = True Range("Q16").Select End Sub Sub View_Print3() ' View_Onscreen Macro Columns("E:E").Hidden = False Columns("S:V").Hidden = False Columns("X:Z").Hidden = False Columns("AB:AB").Hidden = False Columns("AI:AI").Hidden = False Range("Q16").Select End Sub |
Macro to Hide/Unhide various columns
Another variation is to use a 'helper' row in which you place a flag to
denote whether you want to hide the column eg "**HIDE**" then have a macro go to the last column and work backwards testing each cell in the row for the flag to determine whether to hide the column. At my work I have a number of files where columns need to be hidden for presentation purposes but shown during preparation. With this method one can write universal code that sits outside each of those files but operates on each file as long as a helper row is defined. I can post code if anyone is interested. -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "Kevin K" wrote: That seems to work. Thank you Jacob! "Jacob Skaria" wrote: and to further reduce the code you can use the below one liner... Sub View_Onscreen3() Range("E:E,S:V,X:Z,AB:AB,AI:AI").EntireColumn.Hidd en = _ Not Columns("E:E").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: How about using just one macro to do both. If hidden...make visible and if visible hide Sub View_Onscreen3() Columns("E:E").Hidden = Not Columns("E:E").Hidden Columns("S:V").Hidden = Not Columns("S:V").Hidden Columns("X:Z").Hidden = Not Columns("X:Z").Hidden Columns("AB:AB").Hidden = Not Columns("AB:AB").Hidden Columns("AI:AI").Hidden = Not Columns("AI:AI").Hidden End Sub If this post helps click Yes --------------- Jacob Skaria "Kevin K" wrote: I've tried modifying the code below to hide/unhide the columns below, but can't get this to work. It either hide/unhides columns not specified below or it will only run the first line. I don't really know VBA, but am just trying to record macros and modify them. Please help if you have any ideas. Thanks! Sub View_Onscreen3() ' View_Onscreen Macro Columns("E:E").Hidden = True Columns("S:V").Hidden = True Columns("X:Z").Hidden = True Columns("AB:AB").Hidden = True Columns("AI:AI").Hidden = True Range("Q16").Select End Sub Sub View_Print3() ' View_Onscreen Macro Columns("E:E").Hidden = False Columns("S:V").Hidden = False Columns("X:Z").Hidden = False Columns("AB:AB").Hidden = False Columns("AI:AI").Hidden = False Range("Q16").Select End Sub |
Macro to Hide/Unhide various columns
Hi K_Macd,
Thanks for this tip, helped me to resolve a difficult situation (similar to your scenario). regards, DaveU On Aug 26, 7:45*am, K_Macd <kmacdonald "A_T" activ8 ''''''''''''''''''''''''''''''''D O T'''''''''''''''''''''''''''''''' net [S wrote: Another variation is to use a 'helper' row in which you place a flag to denote whether you want to hide the column eg "**HIDE**" then have a macro go to the last column and work backwards testing each cell in the row for the flag to determine whether to hide the column. At my work I have a number of files where columns need to behiddenfor presentation purposes but shown during preparation. With this method one can write universal code that sits outside each of those files but operates on each file as long as a helper row is defined. I can post code if anyone is interested. -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" |
| All times are GMT +1. The time now is 08:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com