OzGrid

How to hide columns in Sheet 2 based on data change in sheet 1

< Back to Search results

 Category: [Excel]  Demo Available 

How to hide columns in Sheet 2 based on data change in sheet 1

 

Requirement:

 

The user is unable to solve a problem of why this code is not working. There are 2 sheets on the worksheet.

 

If the value in cell A1 Sheet 1 = 0 then hide Column C in Sheet 2. Seems simple. The user is using Excel for Mac 2016.


Worksheet is attached and code is in Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target_Address = Range("A1") Then
    If Target_Address = "0" Then
        Sheet2.Columns("C").EntireColumn.Hidden = True
    Else
        Sheet2.Columns("C").EntireColumn.Hidden = False
    End If
End Sub

 

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1201691-hide-columns-in-sheet-2-based-on-data-change-in-sheet-1

 

Solution:

 

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, [a1]) Is Nothing Then
        Sheet2.Columns(3).Hidden = 0
        If Target = 0 Then Sheet2.Columns(3).Hidden = 1
    End If

End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to hide all rows with a blank or zero
How to hide/unhide rows
How to use VBA code to hide row based on month
How to hide/unhide specific name range using VBA

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)