Automatically hide/unhinde rows in columns

[wandaervin]

I have a worksheet that I need to hide rows and columns based on a source file. I would like it to automatically happen each time the file is opened. I am currently using this code but it only updates the columns, I then have to go in and run the second part.

Private Sub Worksheet_Calculate()

Dim ThisCell As Range

Application.ScreenUpdating = False

For Each ThisCell In Range("C2503:AZ2503")
If ThisCell.Value = 0 Then
ThisCell.EntireColumn.Hidden = True
Else
ThisCell.EntireColumn.Hidden = False

End If

Next ThisCell

End Sub

Private Sub Worksheet_Calculate2()

Dim ThatCell As Range

Application.ScreenUpdating = False

For Each ThatCell In Range("B100:b2501")
If ThatCell.Value = 0 Then
ThatCell.EntireRow.Hidden = True
Else
ThatCell.EntireRow.Hidden = False
End If

Next ThatCell

End Sub

[marreco]

Put your code around tags

Try..Ptu in ThisWorkbook

Please Login or Register  to view this content.

[wandaervin]

I am a novice at this so I'm not sure what put your code around tags mean. I did go back and change the code to this

Private Sub Workbook_Open()

Dim ThisCell As Range
Dim ThatCell As Range

Application.ScreenUpdating = False

For Each ThisCell In Range("C2503:AZ2503")
If ThisCell.Value = 0 Then
ThisCell.EntireColumn.Hidden = True
Else
ThisCell.EntireColumn.Hidden = False

End If

Next ThisCell

For Each ThatCell In Range("B100:b2501")
If ThatCell.Value = 0 Then
ThatCell.EntireRow.Hidden = True
Else
ThatCell.EntireRow.Hidden = False
End If


Next ThatCell

End Sub

But it still requires me to manually run the code to work. I would think that by using Sub Workbook_Open would generate the code automatically? Any help would be appreciated

[marreco]

Try the event Private Sub Worksheet_Change (ByVal Target As Range)

Please read the forum rules.