Auto Hide Columns

[amerifax]

I would like to Hide columns a,b,c,d by clicking on a cell that containes the commands to do so. Years in past I had Quatropro that did that for me. I'm just starting with Excell. So if you told me to use a macro, I would not have a clue. I was hoping to see the actuall macro to get me started with Macroes.

Bob

 

[boblarson]

What you should do is what I do. Record a macro of your actions to see what it created and use that, modified as needed. It makes figuring things out in Excel very easy (for the most part).

 

[amerifax]

Thanks for the quick responce. I did just try this and got an error.

Run-Time error '28':
Out of stack space.

Seems like an error I would get years ago.

Sub Hide()
'
' Hide Macro
' Auto hide Columns
'
' Keyboard Shortcut: Ctrl+p
'
Application.Run "FULL_11_Bob2.xlsx!Hide"
Columns("D").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Columns("J:J").Select
Selection.EntireColumn.Hidden = True
Columns("L:L").Select
Selection.EntireColumn.Hidden = True
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
End Sub

Bob

 

[boblarson]

Sub Hide()
'
' Hide Macro
' Auto hide Columns
'
' Keyboard Shortcut: Ctrl+p
'
Columns("A:D").Select
Selection.EntireColumn.Hidden = True
End Sub

 

[amerifax]

I see what you are doing. I am going to try to explain myself a little better. I do understand what you are doing and I see how it works.

I would like to set the Macro to Hide every other column. Here are the points:
File Name is FULL_11_Bob.xlsx

I want to hide columns: D, F, H, J, L etc.

But I would also like to be able to reverse the action and unhide the columns as well.

When I used your example it asked me to save in a Macro Enable Workbook. After I write a macro can't I use it anywhere I want or is it specific to the workbook it was written in?

Thanks for you help. I am just trying to get a handle on how Macros work in an Excel Environment.

Bob

 

[boblarson]

I see what you are doing. I am going to try to explain myself a little better. I do understand what you are doing and I see how it works.

I would like to set the Macro to Hide every other column. Here are the points:

File Name is FULL_11_Bob.xlsx

File name is irrelevant really. You need to add the module (see attached sample) from the sample to that file. If you do then it will be available when that file is open.

I want to hide columns: D, F, H, J, L etc.

But I would also like to be able to reverse the action and unhide the columns as well.

I modified the sample (see attached) so that if you run the code the first time, it hides the columns and then if run again it makes them visible.

When I used your example it asked me to save in a Macro Enable Workbook. After I write a macro can't I use it anywhere I want or is it specific to the workbook it was written in?

Yes, in Excel 2007, if you have any macros in the workbook it needs to be saved as an xlsm file and not xlsx file. This is something you just have to do. I have uploaded an Excel 2003 file (because I don't have 2007 here) so it is an xls file.

 

[amerifax]

Thank you so much for your help. This is our first Excel Macro; thanks to you.

I used to work in Quattro Pro years ago...I was able to build some pretty complex macros and calculations there.

Thanks again,

Bob

 

[boblarson]

No problem, glad to help.