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

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)

Answer
Answer

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

109 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

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

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

Mike H

74 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated September 10, 2020 Views 146,867 Applies to: