VBA hide Column

[Iiaao]

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

 

[Teeroy]

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:

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

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]

Teeroy,

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

Cheers

I

 

[Teeroy]

Glad to help.