Hiding and unhiding column automaticaly when a cell value changes

Originally Posted by titovalle

Hello friends,

This is my first post and I think this forum has really brilliant people cooperating.

My inquirie is that I would like to create a macro in excel that can hide and unhide columns automatically when a cell value changes.

ie. When A1=pizza hide column d and show column c
When A1=burger hid column c and show column d

I want to apply this macro to a file that contains a lot of columns for each type of information.

I will appreciate any help I can get about this.

Hi,

In the worksheet tab, rightmouse and View Code, and paste this into that sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_Range As String = "A1:A1"
If Not Intersect(Target, Me.Range(WS_Range)) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error Resume Next
        With ActiveSheet
            If Cells(1, 1).Value = "burger" Then
                Cells(1, 3).EntireColumn.Hidden = True
                Cells(1, 4).EntireColumn.Hidden = False
                ElseIf Cells(1, 1).Value = "pizza" Then
                    Cells(1, 4).EntireColumn.Hidden = True
                    Cells(1, 3).EntireColumn.Hidden = False
                Else: Cells(1, 3).EntireColumn.Hidden = False
                    Cells(1, 4).EntireColumn.Hidden = False
                    End If
                End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
On Error GoTo 0
End Sub

---

Originally Posted by evilrtc

I found this thread and I need something for a sheet I'm working on that is very similar. What I need to do though is hide an entire row if one cell in that row contains a certain word.

How would I modify the above code to do this?

Hi,

Try

Sub Macro1()
' Hide Rows
Dim iRow As Long
Dim iLastRow As Integer
With ActiveSheet
iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For iRow = iLastRow To 1 Step -1
    If .Cells(iRow, 1) = "a certain word" Then
        .Rows(iRow).EntireRow.Hidden = True
    End If
    Next
    End With
End Sub

will work for the A cell of the row, but I'm not sure how that fits into a thread for columns.

Do you need the code to test any other cells? - is the 'word' the only content of the cell? - you need to confirm what is required.

hth
---