Auto hide columns

[xaritona89]

Hello,
I have an excel file. I want to automatically hide the corresponding columns during opening.
How can this be done?

P.S. With the help of Python, I automatically open Excel and take a picture. When opening, I want it to be hidden if cell = 0, or visible if cell = 1.

 

[hrayani]

Hello,

Try

Private Sub Workbook_Open()
Dim Cl As Range
For Each Cl In Range("C1:M1")
Cl.EntireColumn.Hidden = Cl.Value = 0
Next Cl
End Sub

 

[xaritona89]

Hello,

Try

Private Sub Workbook_Open()
Dim Cl As Range
For Each Cl In Range("C1:M1")
Cl.EntireColumn.Hidden = Cl.Value = 0
Next Cl
End Sub

i try :

Private Sub Workbook_Open()


Dim Cl As Range

    For Each Cl In Range("C1:M1")
        Cl.EntireColumn.Hidden = Cl.Value = 0
    Next Cl

End Sub

Then i open it doesn't work.
I saved the file: microsoft excel macro-enabled worksheet.
Code insert in Module1.

 

[hrayani]

Did you put the code in This Workbook under Microsoft Excel Objects ??

 

[My Aswer Is This]

The script works for me.
Do not put the script in a module

Install the code like this:

This is an auto sheet event script

Your Workbook must be Macro enabled

To install this code:

Right-click on the sheet tab

Double click on this workbook

Paste the code in the VBA edit window

 

[bebo021999]

Let say if 0 and 1 change, the column hide/unhide does not updated, until close the file and reopen.
Therefore, I suggest to use worksheet_activate event.(Right click on sheet name, view code then paste into)

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range, u As Range
Range("C1:M1").EntireColumn.Hidden = False
For Each cell In Range("C1:M1")
    If Not IsEmpty(cell) And cell.Value = 0 Then
        If u Is Nothing Then
            Set u = cell
        Else
            Set u = Union(u, cell)
        End If
    End If
Next
u.EntireColumn.Hidden = True
End Sub

 

[xaritona89]

Private Sub Workbook_Open()


Dim Cl As Range

    For Each Cl In Range("C1:M1")
        Cl.EntireColumn.Hidden = Cl.Value = 0
    Next Cl

End Sub

This code works when I test with a button.
But after saving, when I open it again, the button doesn't work anymore.

Please help me how to save it so that it works when opened.
Where should I put this code?

 

[xaritona89]

Let say if 0 and 1 change, the column hide/unhide does not updated, until close the file and reopen.
Therefore, I suggest to use worksheet_activate event.(Right click on sheet name, view code then paste into)

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range, u As Range
Range("C1:M1").EntireColumn.Hidden = False
For Each cell In Range("C1:M1")
    If Not IsEmpty(cell) And cell.Value = 0 Then
        If u Is Nothing Then
            Set u = cell
        Else
            Set u = Union(u, cell)
        End If
    End If
Next
u.EntireColumn.Hidden = True
End Sub

my file musb be xlss or xlsm?

 

[xaritona89]

Did you put the code in This Workbook under Microsoft Excel Objects ??

 

[bebo021999]

my file musb be xlss or xlsm?

Try to save as .xlsm