Automatically hide rows in Excel based on value per row Hi I

Automatically hide rows in Excel based on value per row

Hi

I have to develop an excelsheet where a row is hidden automatically if the value in cell e is 1 and unhide the row automatically if the value in cell e is 2. And this has to work for all rows

An example:

Row 20, cell E20 = 1 -> automatically hide row

Row 21, cell E21 = 2 -> automatically unhide row

Row 22, cell E22 = 2 -> automatically unhide row

etc.

The values in column E change regularly automatically based on other information in the excelsheet (with an if formula in colume E). And then the row should hide or unhide automatically

Can you please help me?

Thanks a lot

Solution

%R$ight click the sheet tab, view code and paste this code in. Close VB editor.#%&

Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, \"E\").End(xlUp).Row
On Error Resume Next
For Each c In Range(\"E1:E\" & LastRow)
   If c.Value = 1 Then
        c.EntireRow.Hidden = True
    ElseIf c.Value = 2 Then
        c.EntireRow.Hidden = False
    End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub