Hide or Unhide Rows Based on a Cell value Excel VBA

VBA code to hide or unhide rows based on a cell value

This article explains how to write a macro to hide all columns that contain a value in a cell.  This can be any word, text, number, or phrase in a cell of the column.

This article explains how to write a macro to hide all columns that contain a value in a cell.  This can be any word, text, number, or phrase in a cell of the column.

 

Hide All Columns Containing a Value

The following macro will hide all the column containing an X in each cell in row 1.  Here is a brief description of how the code works:

  1. This macro loops through all the cells in Range(“A1:G1”) using a For Loop.
  2. The If statement checks the cell’s value to see if it equals X.
  3. If the cell value equals X then the cell’s entirecolumn is hidden.

You can copy/paste the code to your own workbook.

 

Sub Hide_Columns_Containing_Value()
'Description: This macro will loop through a row and
'hide the column if the cell in row 1 of the column
'has the value of X.
'Author: Jon Acampora, Excel Campus
'Source:
    
Dim c As Range

    For Each c In Range("A1:G1").Cells
        If c.Value = "X" Then
            c.EntireColumn.Hidden = True

            'You can change the property above to False
            'to unhide the columns.
        End If
    Next c

End Sub

Unhide All Columns in a Range

You can change the Hidden property to False to unhide the columns. You could also run the following macro to unhide all the columns at one time.

Sub Unhide_All_Columns()
'This macro will unhide all the columns in the
'specified range.

    Range("A1:G1").EntireColumn.Hidden = False

End Sub

 

Loop Through All Sheets in the Workbook

Kenny asked a question about using this macro on all the sheets in the workbook.  Here is a version of the macro that loops through all the sheets in the workbook, and runs the code on each sheet.

Sub Hide_Columns_Containing_Value_All_Sheets()
'Loop through all sheets and hide columns containing
'a value on each sheet.
    
Dim c As Range
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For Each c In ws.Rows("1:1").Cells
            If c.Value = "X" Then
                c.EntireColumn.Hidden = True
                'You can change the property above to False
                'to unhide the columns.
            End If
        Next c
    Next ws

End Sub

 

 

 

Try More..

 

 

Private Sub PG1(ByVal Target As Range)
    If .Range("E50").Value = "Passed" Then
        Rows("51").EntireRow.Hidden = True
    End If
    ElseIf Range("E50").Value = "Failed" Then
        Rows("51").EntireRow.Hidden = True
    End If
End Sub

 

Try it

Sub PG1()

    If Range("E50").Value = "Passed" Then
        Rows("51:51").EntireRow.Hidden = True
    ElseIf Range("E50").Value = "Failed" Then
        Rows("51:51").EntireRow.Hidden = False
    End If

End Sub

 

Download the Excel File

Download the example Excel file that contains the code samples.

 

 

Hide Columns That Contain A Value In A Cell VBA Macro.xlsm 

 

 

 

 

 

 

 

 

 

 

 

 

Share This Post

One comment

  • Good info and straight to the point. I don’t know if this is in fact the best place to ask but do you guys have any ideea where to employ some professional writers? Thank you 🙂

Leave a Reply

Your email address will not be published.