Need to delete rows after a specific text is found

[zookeepertx]

I've been trying to figure this out on my own, but am just beating my head into a wall. So, I surrender & am hoping someone can tell me where my error is.

I have a report that shows data broken up into age ranges: 1-30 days, 31-60 days, etc. The age range info is found in column B. I want to delete the next 6 rows after each age range header. (All headers look like: Age Range: 0-30. No of recaps 23)

Here's what I've arrived at most recently (just one of many things I've tried), but it just runs through & doesn't actually DO anything. At least it doesn't give me any annoying error messages, so there's that.

Sub ClearingTB()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
 
    LR = Range("B" & Rows.count).End(xlUp).Row
    LC = Cells(12, Columns.count).End(xlToLeft).Column
 
    For i = 1 To LR
        If Range("B" & i).Value = "Age Range:*" Then
        Rows("i + 1 : i + 6").Delete
        End If
    Next i
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
 
End Sub

If someone can show me this, I think I can figure out from there how to do the rest of what needs done on this report.

Thanks!

Jenny

 

[Jonmo1]

The wildcard isn't going to work there...
Try the like operator...

If Range("B" & i).Value Like "Age Range:*" Then


Also, you may want to make your loop go backwards (bottom to top)

change
i = 1 To LR
to
i = LR To 1 Step -1


Hope that helps.

 

[zookeepertx]

Hi Jonmo!

That does make it recognize & TRY to do the deleting, but gives back a "runtime 13 error 13: Type mismatch" on the

Rows("i + 1 : i + 6").Delete

I assume I have my syntax wrong in there.

Thanks

Jenny

 

[Jonmo1]

Ah, yes...

That should probably by

Rows(i + 1 & ":" & i + 6).Delete

 

[zookeepertx]

AWESOME!!!! Thank you SO much!!

Jenny

 

[Jonmo1]

Glad to help, thanks for the feedback