hi Biased Historian,
Here's a slightly different approach which worked on my test data - if I understand your needs correctly...
I haven't used this approach before, would you mind letting me know how fast it runs compared to Dom's version on your real data?
option explicit
Sub test2()
Const pbStr As String = " of " 'page break string
Const NumOfRwsToDel As Long = 43
Const FirstRow As Long = 2
Dim LastRow As Long
Dim i As Long 'reused loop index
Dim rngArr As Variant
Dim ws As Worksheet
Dim pbRowColl As Collection 'page break row collection
Dim curStr As String 'current string
Dim LocOfpbStr As Long 'location of page break string
Set ws = ActiveSheet
Set pbRowColl = New Collection
'create an "in memory" variant array of the info in column A
With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
rngArr = .Range(.Cells(FirstRow, "a"), .Cells(LastRow, "a"))
End With
For i = LBound(rngArr) To UBound(rngArr)
curStr = rngArr(i, 1)
LocOfpbStr = InStr(1, curStr, pbStr)
If LocOfpbStr Then
If IsNumeric(Left(curStr, LocOfpbStr - 1)) And IsNumeric(Right(curStr, Len(curStr) - (LocOfpbStr + Len(pbStr) - 1))) Then
With pbRowColl
.Add Item:=FirstRow - LBound(rngArr) + i
End With
End If
End If
Next i
'loop through the page break row collection backwards & select ('delete) the desired rows
For i = pbRowColl.Count To 1 Step -1
With ws
.Cells(pbRowColl.Item(i), "a").Resize(NumOfRwsToDel, .Columns.Count).Select 'Delete
End With
Next i
'For lngLoopRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
' If Range("A" & lngLoopRow) = "3 of 18" Then
' Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
' End If
'Next lngLoopRow
Set ws = Nothing
Set pbRowColl = Nothing
End Sub
hth
Rob
Bookmarks