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.

 

[Bhonpu]

Hi BiocideJ
I am using the codes we discussed here and they are working fine.
One more suggestion I want from you is:

Let the string is first column of any row is 1000.0 (say) and from there on after two rows we find 200.0 in the first column of new row. If this happens retain the values from row containing 1000.0 to 200.0 otherwise delete it.
I mean if we found 1000.0 and then after 2 rows (in the 3rd including row of 1000.0) if 200.0 is there then keep it otherwise delete everything!

Would you please help me for the same.

 

[ChrisRRR]

Sorry to bump, but I have been looking for something like this for a couple days.

You guys on here always amaze me with your ability to understand how to write this stuff.

 

[yomerves]

I have also been looking for this answer, and found this solution very helpful. However, I have on additional nuance I need help with:

What if I want to use a wild card within the Start and End strings? Like I want to have the text start at Property A and then delete until Total A and then jump to Property B and delete until Total B. I have tried using strStart = "Property:*" but it doesn't work. Is there a way to correct this? Would save me a lot of time of having to put in the individual property names. Thank you very much!

 

[Joe4]

I have also been looking for this answer, and found this solution very helpful. However, I have on additional nuance I need help with:

What if I want to use a wild card within the Start and End strings? Like I want to have the text start at Property A and then delete until Total A and then jump to Property B and delete until Total B. I have tried using strStart = "Property:*" but it doesn't work. Is there a way to correct this? Would save me a lot of time of having to put in the individual property names. Thank you very much!

Welcome to the Board!

It is usually best to post your questions to new threads instead of posting to old threads from 2014 (especially since the person who came up with the code has not posted here since 2019)!
That way it appears as a new unanswered question for ALL to see (many people use the "Unanswered threads" link to look for questions to answer).
Note that you can also includes links to other threads in your questions.

However, see if this does what you want. If you want to see if an entry begins with the phrase "Property:", you can do something like:

If Len(Range("C" & r), 9) = "Property:" Then

 

[yomerves]

Thanks for replying! Yes I wasn't sure if it was better to create a new thread or post the question in this thread since it piggybacks off the conversation here. When I replaced part of my code with this code, I get a Compiler error saying that I am missing an ), so I'm trying to troubleshoot and see what is happening.

 

[Joe4]

Thanks for replying! Yes I wasn't sure if it was better to create a new thread or post the question in this thread since it piggybacks off the conversation here. When I replaced part of my code with this code, I get a Compiler error saying that I am missing an ), so I'm trying to troubleshoot and see what is happening.

You are welcome. If you cannot find it, you can post your code.

 

[yomerves]

This is what I have and I am getting a compiler error: It seems to want to replace the comma after the range with a closed parenthesis

Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = InStr(1, "Property:")
    strEnd = "Charge Post Month: 08/2022"
    
    DELETEMODE = False
    For r = 1 To Range("A" & Rows.Count).End(xlUp).Row  'first to last used row
        
        If Range("A" & 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 Len(Range("A" & r),9) = "Property:" Then DELETEMODE = True
    Next r

 

[MARK858]

Try changing the word LEN to LEFT

 

[Joe4]

Try changing the word LEN to LEFT

Oh my gosh! I cannot believe I did that! Thanks for the catch, Mark!
It is one of those errors that you make that no matter how many times you look at it, you see what you thought you typed, and not what you actually typed.

Sorry for the confusion!

 

[MARK858]

@Joe4 no problem, we all do it. I also think the OP will have to consider where they have placed the line