Macro to Hide/Unhide various columns

K

Kevin K

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
 
J

Jacob Skaria

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
 
J

Jacob Skaria

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.Hidden = _
Not Columns("E:E").Hidden
End Sub

If this post helps click Yes
 
K

Kevin K

That seems to work. Thank you Jacob!

Jacob Skaria said:
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.Hidden = _
Not Columns("E:E").Hidden
End Sub

If this post helps click Yes
 
K

K_Macd

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.
 
D

Dave

Hi K_Macd,

Thanks for this tip, helped me to resolve a difficult situation
(similar to your scenario).

regards,

DaveU
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top