Excel delete number of rows if cells equals certain text

[soldier2gud4me]

Hi there,

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

 

[Joe4]

Here is a little macro that I think should do what you want.

Sub Delete6Rows()

    Dim r As Long
   
    Application.ScreenUpdating = False
   
    Worksheets("Summary").Activate

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

 

[soldier2gud4me]

Here is a little macro that I think should do what you want.

Sub Delete6Rows()

    Dim r As Long
  
    Application.ScreenUpdating = False
  
    Worksheets("Summary").Activate

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

It works !! Thank you very much. I was advised to try the function .Resize as well.

 

[Joe4]

You are welcome.
Glad I was able to help.

In Excel, there are often many different ways to accomplish a task.
So you could replace this line:

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

with something like this:

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

and it should work the same.

 

[soldier2gud4me]

You are welcome.
Glad I was able to help.

In Excel, there are often many different ways to accomplish a task.
So you could replace this line:

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

with something like this:

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

and it should work the same.

Thank you very much