VBA to Unhide and Hide Columns

dtran22

New Member
Joined
Oct 20, 2002
Messages
7
Hi all,

I want to create two buttons.. one name HIDE and the other UNHIDE.

What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)

For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)

any help would be appreciated?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Activate the Control ToolBox and add a CommandButton to your worksheet. Right click the button and choose View Code. Paste this into the window on the right:

Code:
Private Sub CommandButton1_Click()
    CommandButton1.TopLeftCell.Offset(0, 1).Resize(, 4).EntireColumn.Hidden = True
End Sub

Not you will only need to add the middle line to the existing procedure stub. Press Alt+F11 to return to your worksheet and click the Design icon on the Control ToolBox.

I will leave you to work out how to add the UnHide button.
 

dtran22

New Member
Joined
Oct 20, 2002
Messages
7
Looks like i didn't describe my problem completely.

I have another macro i run each day that copies the data from sheet1 to sheet2 and then hide all the columns in sheet2 except for the date column... basically everyday i run the update macro it inserts new columns and hide so i basically end up seeing just date columns with the data columns being hidden in between. I want to be able to copy the button each time i run the update marco and have it stay within the date column and only unhide or hide the fixed amount of columns to the right of it.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sounds like you can do this in your copy macro, without the need for a button.

Post the code.
 

dtran22

New Member
Joined
Oct 20, 2002
Messages
7

ADVERTISEMENT

Sub DataRollover()
'
' DataRollover Macro
' Macro recorded 10/22/02 by David T. Tran
'
' Keyboard Shortcut: Ctrl+z
'
Sheets("Sheet2").Select
Columns("B:J").Select
Selection.Copy
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C"
Range("B2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:D86"), Type:=xlFillDefault
Range("C3:D86").Select
ActiveWindow.SmallScroll Down:=-75
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E86")
Range("E3:E86").Select
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F86")
Range("F3:F86").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G86")
Range("G3:G86").Select
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H10")
Range("H3:H10").Select
Range("H10").Select
Selection.Copy
Range("H11:H86").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-69
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I86")
Range("I3:I86").Select
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J86")
Range("J3:J86").Select
Range("C3:J86").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("B8:F91").Select
Selection.ClearContents
Range("H7:I91").Select
Range("H91").Activate
Selection.ClearContents
Range("A5").Select
Sheets("Sheet2").Select
Columns("L:R").Select
ActiveWindow.SmallScroll ToRight:=9
Columns("L:S").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=-9
Columns("C:J").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet1").Select
Range("A7").Select
End

I want to be able to copy the Hide and Unhide button into Sheet2 Column B each time i run this macro.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why don't you manually put the button in cell A1 of Sheet2 and change the code to:

CommandButton1.TopLeftCell.Offset(0, 2).Resize(, 4).EntireColumn.Hidden = True

That would probably be quicker because I always get an irresistible urge to completely rewrite recorded macros.
 

dtran22

New Member
Joined
Oct 20, 2002
Messages
7

ADVERTISEMENT

because lets say on Oct 27th I wants to see my hidden data on Oct 10th.. i know i can just high betwene the two dates but if i had a button to do it it'll be great.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This code will add a button in cell B1 on Sheet2. Clicking the button will alternately hide/unhide the four columns to the right.

Code:
Sub AddButton()
    Dim Top As Single
    Dim Left As Single
    Dim Width As Single
    Dim Height As Single
    Dim Btn As Button
    Top = Sheets("Sheet2").Cells(1, 2).Top
    Left = Sheets("Sheet2").Cells(1, 2).Left
    Width = Sheets("Sheet2").Cells(1, 2).Width
    Height = Sheets("Sheet2").Cells(1, 2).Height * 2
    Set Btn = Sheets("Sheet2").Buttons.Add(Left, Top, Width, Height)
    With Btn
        With .Characters
            .Text = "Hide/" & Chr(10) & "Unhide"
            With .Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 8
            End With
        End With
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .OnAction = "ToggleHide"
    End With
End Sub

Sub ToggleHide()
    Dim BtnName As String
    Dim Rng As Range
    BtnName = Application.Caller
    Set Rng = ActiveSheet.Buttons(BtnName).TopLeftCell.Offset(0, 1).Resize(, 4).EntireColumn
    Rng.Hidden = Not Rng.Hidden
End Sub
 

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
excuse the intrusion - how would you (or where would you) modify the code to instead of hiding the 4 columns, hide/unhide columns based on their names? any hints/tips would be appreciated. If you dont mind - i may stand on the shoulder of giants and use your code if i can adapt it for my purpose - thanks.
 

anil_3

New Member
Joined
Jan 10, 2012
Messages
4
Hi All,

I have learnt so much from the people who have shared they knowledge in this website and thank you so much.

I need the below macro to be edit like, every second column should be hide.

for example: when i click the button the col's B, D,F,G.... should hid showing col's A, C, D....

Thanks for ur help in advance.
 

Forum statistics

Threads
1,144,149
Messages
5,722,794
Members
422,459
Latest member
Chriselff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top