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

 

[dmt32]

Hi,
try

Dim r As Long
    For r = 4 To 50 Step 3
        Cells(r, 1).Offset(1).Resize(2).EntireRow.Hidden = Len(Cells(r, 1).Value) > 0
    Next r

Dave

 

[mazher]

Thanks Dave it was exactly what I was looking.

Please can you alter the above code so that starting and ending row numbers can be entered in cell B1 and cell C1.

 

[FryGirl]

Hi, maybe this!

Sub HideRows()
    Dim r As Long
    For r = Cells(1, 2) To Cells(1, 3) Step 3
        Cells(r, 1).Offset(1).Resize(2).EntireRow.Hidden = Len(Cells(r, 1).Value) > 0
    Next r
End Sub

 

[mazher]

Thanks FryGirl

 

[dmt32]

Thanks Dave it was exactly what I was looking.

Please can you alter the above code so that starting and ending row numbers can be entered in cell B1 and cell C1.

Looks like @FryGirl beat me to it - glad suggestion does what you want

Dave

 

[mazher]

@dmt32 , @FryGirl Taking it a bit further
I have module with the following code in it but unfortunately its not working My data is in Sheet 2 and i have no idea when its no working

Sub Check()
If Range("A1").Value = 1 Then
    Call Hide
ElseIf Range("A1").Value = "" Then
    Call Unhide
Else
    Return
End If
End Sub

Sub Hide()
Application.ScreenUpdating = False
Dim r As Long
    For r = 4 To 50 Step 3
        Cells(r, 1).Offset(1).Resize(2).EntireRow.Hidden = Len(Cells(r, 1).Value) > 0
    Next r
Application.ScreenUpdating = True
End Sub

Sub Unhide()
Application.ScreenUpdating = False
Range("A4:A50").EntireRow.Select
Selection.EntireRow.Hidden = False
Range("A4").Select
End Sub

 

[dmt32]

Hide,
suggestion I posted will both hide or unhide the rows based on the condition being met (cell has a value) which is what I understood you wanted?
Also, code is unqualified & needs to be run with correct sheet active. If this is not possible, then qualify the ranges to correct sheet

Dave

 

[FryGirl]

If you want the macro to run on a specific sheet, then you need to qualify the sheet. I did not touch your first sub as I don't know what sheet that is intended for.

Sub Hide()
Application.ScreenUpdating = False
Dim r As Long
Dim ws As Worksheet: Set ws = Sheets("Sheet2")
For r = 4 To 50 Step 3
ws.Cells(r, 1).Offset(1).Resize(2).EntireRow.Hidden = Len(ws.Cells(r, 1).Value) > 0
Next r
Application.ScreenUpdating = True
End Sub

Sub Unhide()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A4:A50").EntireRow.Hidden = False
End Sub

 

[dmt32]

looking at your post further if you want to specify Hide or Unhide instruction then you can do this with a little modification to suggestion & use a common code.

try following & see if does what you want

Sub Check()
    If Range("A1").Value = 1 Then
        Call HideUnhide(xlHidden)
    ElseIf Range("A1").Value = "" Then
        Call HideUnhide(xlVisible)
    End If
End Sub

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

In this update, I have qualified the ranges - you will need to change sheet name as required

Hope Helpful

Dave