How to delete number of rows if cell meets criteria excel vba

Question

Im looking for a way to delete a total of 6 rows if a cell (C20) in the first of those 6 rows contains "#REF!". Here's what i have so far :

Sub Button7_Click()

Dim srchRng As Range

Worksheets("Summary").Activate

ActiveWindow.DisplayFormulas = False


Set srchRng = Range("C20:C300")

Dim c As Range
For Each c In srchRng
If c.Formula = "=#REF!" Then
    ActiveWorkbook.Worksheets("Aug").Columns(2).SpecialCells(xlFormulas,     xlErrors).EntireRow.Delete
              
    Exit For
End If
Next

End Sub

Answer 1

Here is the solution to my question :

Sub Button7_Click()

Dim r As Long

Application.ScreenUpdating = False

Worksheets("Summary").Activate

 '   To Loop through rows backwards
   For r = 300 To 20 Step -1
 '     To Check the formula in column C
      If Cells(r, "C").Formula = "=#REF!" Then
 '         To Delete row and 5 rows under it
        Rows(r & ":" & r + 5).Delete
    End If
 Next r

Application.ScreenUpdating = True

MsgBox "Done!"

End Sub

You could also replace this line:

       Rows(r & ":" & r + 5).Delete

with something like this:

       Cells(r, "C").Resize(6, 1).EntireRow.Delete