Automatically hide rows in Excel based on value per row

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

Monika

PS I've read through answers related to my question, but they unfortunately didn't help me as the related quesitons were similar, but not the same (and I didn't get how the macros are built and thus wasn't able to adapt them)

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Mike H..

Hi,

Right 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

If this response answers your question then please mark as answer.

Mike H

118 people found this reply helpful

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer

Mike H..

Hi,

We need to do things slightly differently to make it work from a button. Delete the code I gave you then while still in VB editor,

Right click ThisWorkbook and insert module and paste this new version of the code in on the right. Assign a button to this code and you're done.

Sub Hide_E()
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

Contribute to the Microsoft 365 and Office forum!

Excel Forum Top Contributors:

Automatically hide rows in Excel based on value per row

Replies (22)

Question Info

Automatically hide rows in Excel based on value per row

Replies (22) 

Question Info

Replies (22)