Macro that will HIDE and UNHIDE columns

Chris The Rock · Mar 15th, 2002, 12:08 PM

I have a work book with many columns. Each column represents a new day.

Because this workbook will be used for a long time, I've had to include far more columns than I presently need, so many of the columns do not contain any data. At any one time, I'd like only 10 columns to be visible.

What I'd like to do is have a macro button that the user can click that will UNhide the next 4 columns to the right, while hiding the 4 leftmost visible columns, maintaining 10 visible columns.

I can explain this better if needed, but these are the basics.

Chris The Rock · Mar 15th, 2002, 12:42 PM

Is this hard to do?

[ This Message was edited by: Chris The Rock on 2002-03-15 11:43 ]

Al Chara · Mar 15th, 2002, 12:50 PM

This should work (its not great coding though):
Sub HideColumns()
'assumes only 10 columns are showing to begin
'hides first 4 columns
Windows(1).VisibleRange.Cells(1, 1).Select
Range(Selection, ActiveCell.Offset(0, 3)).Select
Selection.EntireColumn.Hidden = True
'unhides next 4 columns
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, ActiveCell.Offset(0, 3)).Select
Selection.EntireColumn.Hidden = False
End Sub

Hope this helps.
Kind regards, Al.

Chris The Rock · Mar 15th, 2002, 01:59 PM

Al,
Thanks for your help. I took the basic idea from your example, and formed it into exactly what I needed.
Again, many thanks.

Mar 15th, 2002, 12:08 PM	#1
Chris The Rock Board Regular Join Date: Feb 2002 Location: Longmont, CO Posts: 279	I have a work book with many columns. Each column represents a new day. Because this workbook will be used for a long time, I've had to include far more columns than I presently need, so many of the columns do not contain any data. At any one time, I'd like only 10 columns to be visible. What I'd like to do is have a macro button that the user can click that will UNhide the next 4 columns to the right, while hiding the 4 leftmost visible columns, maintaining 10 visible columns. I can explain this better if needed, but these are the basics. __________________ "Never go in against a Sicilian when death is on the line!"

Mar 15th, 2002, 01:59 PM	#4
Chris The Rock Board Regular Join Date: Feb 2002 Location: Longmont, CO Posts: 279	Al, Thanks for your help. I took the basic idea from your example, and formed it into exactly what I needed. Again, many thanks. __________________ "Never go in against a Sicilian when death is on the line!"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 12:42 PM	#2
Chris The Rock Board Regular Join Date: Feb 2002 Location: Longmont, CO Posts: 279	Is this hard to do? [ This Message was edited by: Chris The Rock on 2002-03-15 11:43 ]

Mar 15th, 2002, 12:50 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	This should work (its not great coding though): Sub HideColumns() 'assumes only 10 columns are showing to begin 'hides first 4 columns Windows(1).VisibleRange.Cells(1, 1).Select Range(Selection, ActiveCell.Offset(0, 3)).Select Selection.EntireColumn.Hidden = True 'unhides next 4 columns Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Range(Selection, ActiveCell.Offset(0, 3)).Select Selection.EntireColumn.Hidden = False End Sub Hope this helps. Kind regards, Al.