Deleting all rows between two strings

Hello,

I’d be grateful for your help making a macro to delete all rows between two specified strings.

It would look for two strings, always in column A: "Item Title" and any string beginning "Library" or "LIBRARY".

It would delete all the rows between these cells (including the rows in which these two strings are found).

Notes:
- "Item Title" and "Library*" may appear multiple times in the worksheet, so multiple ranges may require deletion.
- The number of rows between these two strings is unpredictable.
- There may be up to 1,000 rows to check through.

Example Workbook:
The attachment shows a typical initial state. For clarity, I’ve highlighted in red two ranges that I’d like the macro to remove (they wouldn’t be highlighted in "real life"!).

range_deletion_question.xlsx

Thank you for any help you can offer.
Matt

Hello Walruseggman. Liking the Beatles reference.

Thank you very much for offering a solution; unfortunately nothing seems to happen when I run it - not even an error.

I've attached the workbook, now with the macro. You'll probably find it works fine and I'm probably being a moron...

Attachment 362617

Best wishes,

Matt

Yeah, I probably should have looked at your example. I wasn't aware that the Library cells would have more text in them than just the word Library.

Anyway, I've tweeked the code and it does delete all your red text cells in your original sheet (the link to the new one you posted doesn't seem to work.)

Let me know how it goes.

Sub mfd()
Dim FirstRow, LastRow, EndRow

EndRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = EndRow To 1 Step -1
    
    If UCase(Cells(i, 1).Value) Like "LIBRARY*" Then
        LastRow = i
        FirstRow = ""
    ElseIf Cells(i, 1).Value = "Item Title" And LastRow <> "" Then
        FirstRow = i
    End If
    
    If LastRow <> "" And FirstRow <> "" Then
    Rows(FirstRow & ":" & LastRow).EntireRow.Delete
    FirstRow = ""
    LastRow = ""
    End If
Next
End Sub

You're an absolute legend. Thank you very much, that works great!

Cool. Reputation would be appreciated (the * in the lower left of posts), and dont forget to mark this thread as SOLVED

Maybe:

Sub mfd()
Dim w As Range
Dim x As Long
Dim z As Long
Dim y As Long
y = Range("A" & Rows.Count).End(3)(2).Row
Cells(y, 1).Select
Do Until ActiveCell.Row < 14
Set w = Range(Cells(2, 1), Cells(y, 1)).Find("*Library*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not w Is Nothing Then
        x = w.Row
    End If
Set w = Nothing
Cells(x, 1).Select
Do Until ActiveCell.Offset(-1) = "Item Title" Or ActiveCell.Row = 3
    ActiveCell.Offset(-1).Select
Loop
z = ActiveCell.Row - 1
Range(Cells(z, 1), Cells(x, 1)).EntireRow.Delete
y = ActiveCell.Row - 2
Cells(y, 1).Select
Loop
End Sub

Hi John - that worked perfectly too. Thank you very much indeed for your time.