Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro to Hide/Unhide various columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default 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"
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Hide-Unhide Columns with a macro Jim Thomlinson Excel Discussion (Misc queries) 1 March 8th 07 07:04 PM
I set up a macro to hide/unhide columns. It hides more columns Lori Excel Programming 1 September 6th 06 04:08 PM
macro to hide/unhide columns Monique Excel Programming 5 August 4th 06 04:32 PM
How do I hide or unhide zero value columns/rows USING A MACRO? paul Excel Discussion (Misc queries) 1 January 23rd 06 03:06 PM
Macro to hide or unhide columns Chance224 Excel Programming 1 February 14th 05 09:34 PM


All times are GMT +1. The time now is 05:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement