Any idea how to shorten that VBA code for Hiding Rows

[mazher]

Range("A5,A6,A8,A9,A11,A12,A14,A15,A17,A18,A20,A21,A23,A24,A26,A27").EntireRow.Select
Selection.EntireRow.Hidden = True

1) Please can someone help me to shorten the above code.

2) Basically I need a a code to hide rows in the range (Row range 4:50 )

If there is a value in cell in Cell A4, I need to hide the rows (5:6), if there is a data in cell A7 in need to hide rows (8:9) and so on like that.

Thanks in advance

 

[mazher]

Thanks again @FryGirl and @dmt32

I do not know when I added your code in the module @dmt32, its not hiding the rows.

Might be I am doing some thing wrong.

 

[mazher]

Sorry my bad @dmt32

I was of the view that only by writing 1 in cell A1 will run the macro, but I have to run the macro manually.

Any chance it can be done like that, I have data in the Sheet2

Thanks again for all your help.

 

[dmt32]

Sorry my bad @dmt32

I was of the view that only by writing 1 in cell A1 will run the macro, but I have to run the macro manually.

Any chance it can be done like that, I have data in the Sheet2

Thanks again for all your help.

By Automatically does this mean A1 value is being changed by formula?

Dave

 

[mazher]

By Automatically does this mean A1 value is being changed by formula?

Dave

Thanks for your reply.

I mean to say when i write 1 in cell A1 the code is executed and rows are hidden, when I delete 1 in cell A1 ( when its empty ) the rows are visible.

Hope this helps

 

[dmt32]

Thanks for your reply.

I mean to say when i write 1 in cell A1 the code is executed and rows are hidden, when I delete 1 in cell A1 ( when its empty ) the rows are visible.

Hope this helps

Hi,
delete earlier code & place ALL following code in your worksheets code page (Right Click Tab > View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Target.Value = 1 Then Call HideUnhide(xlHidden)
        If Target.Value = "" Then Call HideUnhide(xlVisible)
    End If
End Sub

Sub HideUnhide(ByVal RowVisibility As Integer)
    Dim r As Long
    For r = 4 To 50 Step 3
            Cells(r, 1).Offset(1).Resize(2).EntireRow.Hidden = _
        IIf(RowVisibility = xlHidden, CBool(Cells(r, 1).Value > 0), False)
    Next r
End Sub

and see if this does what you want

Note: Solution is based on you making direct changes to Range A1 & not by formula.

Dave

 

[mazher]

Extremely thankful Dave @dmt32

Its working perfectly fine.

 

[dmt32]

Extremely thankful Dave @dmt32

Its working perfectly fine.

Most welcome, glad solution worked for you

many thanks for feedback

Dave