0

How do I hide columns of the sheet when I move away from the sheet?

Private Sub Worksheet_Deactivate()
    Columns("A:K").Select
    Selection.EntireColumn.Hidden = True
    Sheets("MASTER").Select
End Sub

The sheet I want the columns to be hidden in is sheet reference.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Lim
  • 75
  • 1
  • 7

2 Answers2

1

Since you are deactivating worksheet, the default worksheet becomes the active worksheet that you swithced to, so when using ranges without sheet reference, i.e. Columns("A:K"), they refer to columns in current worksheet. That's why you should always use references to worksheets, i.e.

Dim ws As Worksheet
Set ws = Worksheets("reference")

And then use it to reference ranges in it:

ws.Columns("A:K").EntireColumn.Hidden = True

Not that I got rid out of the Select method, which is very discouraged to use.

This might be really helpful for you: How to avoid using Select in Excel VBA

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
0

Columns("A:K").EntireColumn.Hidden = True

Lee Li Fong
  • 274
  • 1
  • 6