VBA hide Column

Iiaao

New Member
Joined
Jan 22, 2014
Messages
45
This what I thought was a simple bit of code falls over when I ask it to hide a column on a different sheet, any ideas why changing sheet is the problem. Below in red is the line where it fails. Thanks in advance.

Private Sub CommandButton1_Click()
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet2").Select
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet2").Select
Columns("U:U").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet3").Select
Columns("V:W").Select
Selection.EntireColumn.Hidden = True
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
I'm guessing that you have this code in a sheet code module rather than a standard code module. If this is the case then the parent object of column "T" (after you have selected Sheet2) is the sheet whose code module you have placed the code (rather than Sheet2) and there is a conflict.

Notwithstanding the above this looks like code developed from the macro recorder. This is a good place to start, but it doesn't give very efficient code as you very rarely need to use Select or Activate in reality.

Have a look at the following which should work for you:

Code:
Private Sub CommandButton1_Click()
Columns("N").EntireColumn.Hidden = True
Columns("Q").EntireColumn.Hidden = True
Columns("T").EntireColumn.Hidden = True
With Sheets("Sheet2")
    'the leading . binds the rest of the code in the block
    'to the object in With statement
    'In this case it is overkill since you only have
    'one statement, but it is good technique.
    .Columns("T:U").EntireColumn.Hidden = True
End With
With Sheets("Sheet3")
    .Columns("V:W").EntireColumn.Hidden = True
End With
End Sub

or since there is only one line in each with block

Code:
Private Sub CommandButton1_Click()
Columns("N").EntireColumn.Hidden = True
Columns("Q").EntireColumn.Hidden = True
Columns("T").EntireColumn.Hidden = True
Sheets("Sheet2").Columns("T:U").EntireColumn.Hidden = True
Sheets("Sheet3").Columns("V:W").EntireColumn.Hidden = True
End Sub
 

Iiaao

New Member
Joined
Jan 22, 2014
Messages
45
Teeroy,

Thanks very much that worked perfectly and made sense, I am just starting out on my journey of learning VBA.

Cheers

I
 

Watch MrExcel Video

Forum statistics

Threads
1,126,906
Messages
5,621,581
Members
415,846
Latest member
nigeywigey

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