Macro request to delete all rows between two specific text values whose rows are dynamic

[crab transplant]

Hello excel guru's,

I have a data output that has two text values which repeat in rows throughout the dataset, Text A and Text B.

The number of rows between Text A and Text B are dynamic, but Text A and Text B are always the bookends and always reside in Column C. What I am looking for is to delete the rows between Text A and Text B throughout the worksheet using a Macro. Additionally there are other rows, not between Text A and Text B that should not be deleted.

Additionally I would explain the problem as: For every instance of Text A, delete rows until instance of Text B. Then look for next instance of Text A and repeat.

I have tried, but failed to adopt these posts and macros http://www.mrexcel.com/forum/excel-...-between-two-other-rows-certain-criteria.html and http://www.mrexcel.com/forum/excel-questions/6483-delete-rows-between-two-specified-values.html as I am a Marco Noob.

Thank you in advance for your assistance.

 

[BiocideJ]

I BELIEVE this is what you are looking for. Since macros cannot be undone, I strongly suggest you save your workbook before running the macro though.
It should run pretty quick too since there is only a single DELETE statement.

Macro runs on the currently active sheet and you can change your bookend values at the very beginning of the code by overwriting the "Text A", "Text B" string values.

Sub DeletebyBookends()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "Text A"
    strEnd = "Text B"
    
    DELETEMODE = False
    For r = 1 To Range("C" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If Range("C" & r).Value = strEnd Then DELETEMODE = False
        
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
        
        If Range("C" & r).Value = strStart Then DELETEMODE = True
    Next r
    
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
    
End Sub

 

[crab transplant]

Thank you BiocideJ, this worked perfectly!

 

[Bhonpu]

Hi, Thanks for your nice macro code. My problem is a lot similar to your solution just with one modification. I want to delete all rows INCLUDING the rows with strings "Text A" and "Text B".

Thanks!

 

[tocy777]

You can use this to delete rows between two strings

On Error Resume Next
Range(Range("C:C").Find("Text A"), Range("C:C").Find("Text B")).EntireRow.Delete
On Error GoTo 0

or just clear it's contents:

On Error Resume Next
Range(Range("C:C").Find("Text A"), Range("C:C").Find("Text B")).Clearcontents
On Error GoTo 0

 

[BiocideJ]

Hi, Thanks for your nice macro code. My problem is a lot similar to your solution just with one modification. I want to delete all rows INCLUDING the rows with strings "Text A" and "Text B".

Thanks!

Relatively straightforward change to my previous code accomplishes this. Simply swap the two lines of when DELETEMODE gets turned on/off.

Sub DeletebyBookends_INCLUSIVE()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "Text A"
    strEnd = "Text B"
    
    DELETEMODE = False
    For r = 1 To Range("C" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If Range("C" & r).Value = strStart Then DELETEMODE = True
        
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
        
        If Range("C" & r).Value = strEnd Then DELETEMODE = False
    Next r
    
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
    
End Sub

Tocy777, your code is also good and a clever single line approach. The only thing you would need to add would be looping as the original request was for multiple instances of the TextA/TextB in the sheet. The only potential problem I imagine with yours would be if there was a stray TextB PRIOR to the first TextA. In that instance, your code would delete all of the rows that were supposed to be kept and leave all of the rows that are supposed to be deleted.

 

[Bhonpu]

Hi BiocideJ
The modified code is not working the way I want. What I require is that for every instance of "Text A", delete rows until instance of "Text B" (Rows that contain Text A and Text B are also need to be deleted). Then look for next instance of Text A until Text B and repeat in the similar way. Your first code did fine except it included Text A and Text b and deleted all rows in-between. I just want that all Rows starting from Text A until Text B are deleted every-time.

My data is also similar to the one posted in the question initially.

 

[Bhonpu]

Hi BiocideJ, sorry for the last reply. I was doing a small mistake at my end. I didnt change the strEnd option. Your code works fine.
Thanks for your quick help!

 

[BiocideJ]

Glad it is working. Hope you have a great day.

 

[BiocideJ]

Also, from a PM the following two versions will keep either the FIRST value (TEXT A)

Sub DeletebyBookends_KEEP_FIRST()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "Text A"
    strEnd = "Text B"
    
    DELETEMODE = False
    For r = 1 To Range("C" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
                
        If Range("C" & r).Value = strStart Then DELETEMODE = True
        If Range("C" & r).Value = strEnd Then DELETEMODE = False
        
    Next r
    
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
    
End Sub

Or the LAST value (TEXT B)

Sub DeletebyBookends_KEEP_LAST()
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "Text A"
    strEnd = "Text B"
    
    DELETEMODE = False
    For r = 1 To Range("C" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If Range("C" & r).Value = strStart Then DELETEMODE = True
        If Range("C" & r).Value = strEnd Then DELETEMODE = False
        
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
        
    Next r
    
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
    
End Sub