CODEWITHSUNDEEP
vbaexcel
Lim
Lim

Reputation: 75

Sheet deactive to hide the columns i want

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.

Upvotes: 0

Views: 38

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37500

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

Upvotes: 1

Lee Li Fong
Lee Li Fong

Reputation: 259

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

Upvotes: 0

Related Questions