If cells in column are blank/not blank, hide/show other columns

  • Hi,

    I'm trying to write VBA code so that when the cells in column D are empty, the columns E to Z are hidden. When something is written in the cell the columns should unhide.

    Hiding the columns has already works, but unhiding doesn't seem to work.

    Anybody that can help with my problem?

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, Columns(5)) Is Nothing Then
    3. If Target.Text = Blank Then
    4. Columns("I").EntireColumn.Hidden = True
    5. ElseIf Target.Text = NotBlank Then
    6. Columns("I").EntireColumn.Hidden = False
    7. End If
    8. End If
    9. End Sub
  • Your code is testing Column E.


    I don't know how how you expect it to work. Once a cell in D has a value entered it would open the range, but what about entries into other cells in D?


    The Change event will only run when an entry is made in D.


    You need something like this


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
    3. If Target.Value = Empty Then
    4. Columns("I").EntireColumn.Hidden = True
    5. Else: Columns("E:Z").EntireColumn.Hidden = False
    6. End If
    7. End Sub
  • I indeed had a error in the initial code.
    After some trail and error i got my inital setup working using the following.

    Code
    1. If Not Intersect(Target, Columns(4)) Is Nothing Then
    2. If Target.Text = Blank Then
    3. Columns("F").EntireColumn.Hidden = True
    4. Else
    5. Columns("F").EntireColumn.Hidden = False
    6. End If
    7. End If

    However the method you suggested using "Target.value" also works. Thanks for the help!