VBA for If X value exists, hide that row, 4 rows before, and 1 row after?

[MrBartlett]

Hi all,

I am wondering if someone could kindly help me with this problem.

If a cell contains value Z, I want to hide that row, X rows before, and Y rows after.

So, via the example above, for the range B1:B15, if a cell contains "#n/a" then hide row 14, rows 9:12, and row 15 (the highlighted rows). I want this to loop, where either we have it check every cell in B1:B15, or for it to check every nth cell, where n = 7.

Thanks in advance!

 

[nemmi69]

Sub HidRows()


Dim Rloop As Long
Dim Xrows As Integer
Dim Yrows As Integer


Xrows = 2
Yrows = 3


'for the range B1:B15. uncomment "step 7" to go in steps of 7
For Rloop = 1 To 15 'Step 7
'If a cell contains value Z, I want to hide that row, X rows before, and Y rows after
    If Application.WorksheetFunction.IsNA(Worksheets("Sheet1").Range("B" & Rloop).Value) = True Then
        Rows(Rloop & ":" & Rloop).EntireRow.Hidden = True
        Rows(Rloop - Xrows & ":" & Rloop - Xrows).EntireRow.Hidden = True
        Rows(Rloop - Yrows & ":" & Rloop - Yrows).EntireRow.Hidden = True


    End If
Next Rloop


End Sub

 

[MrBartlett]

Sub HidRows()


Dim Rloop As Long
Dim Xrows As Integer
Dim Yrows As Integer


Xrows = 2
Yrows = 3


'for the range B1:B15. uncomment "step 7" to go in steps of 7
For Rloop = 1 To 15 'Step 7
'If a cell contains value Z, I want to hide that row, X rows before, and Y rows after
    If Application.WorksheetFunction.IsNA(Worksheets("Sheet1").Range("B" & Rloop).Value) = True Then
        Rows(Rloop & ":" & Rloop).EntireRow.Hidden = True
        Rows(Rloop - Xrows & ":" & Rloop - Xrows).EntireRow.Hidden = True
        Rows(Rloop - Yrows & ":" & Rloop - Yrows).EntireRow.Hidden = True


    End If
Next Rloop


End Sub

Thanks for the response! Any ideas why I get a type 13 error on this line?

Rows(Rloop - Xrows & ":" & Rloop - Xrows).EntireRow.Hidden = True

 

[MrBartlett]

Figured it out. It was trying to hide a row less than 0 lol.

I'm gonna go to bed and test this out in the morning.

 

[nemmi69]

Figured it out. It was trying to hide a row less than 0 lol.

I'm gonna go to bed and test this out in the morning.

Happens

 

[MrBartlett]

Happens

Everything ended up working well, thanks again!