VBA delete rows based on phrase

I have a vba code to name ranges.

However, to work correctly, the phrase it identifies "Continental U.S. Ground" has to be 2 lines above the phrase "Weight (in Lbs )".
Is there any way to identify these two phrases in Column A and remove any additional lines between them?

Needs to be in column A on sheet "Net Rates 1"
"Continental U.S. Ground"
-EMPTY ROW-
"Weight (in Lbs )"

Help??
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh VCommented:
Can you send me data?
Euro5Author Commented:
The data could be any number of rows in between.
I think I also need to run remove merge and wrap also to get this to work consistently, can you help me do that?

THANK YOU!!
sample.xlsx
Glenn RayExcel VBA DeveloperCommented:
This code will reset the word wrap and merging anchored on cell A2 and then remove all rows but one between A2 and the top row of the table.

Option Explicit
Sub Clear_Rows()
    With Range("A2")
        .HorizontalAlignment = xlGeneral
        .UnMerge
        .WrapText = False
    End With
    Range("A3").Select
    Do Until ActiveCell.Offset(1, 0).Value = "Weight (in Lbs )"
        ActiveCell.EntireRow.Delete
    Loop
End Sub

Open in new window


Regards,
-Glenn
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Euro5Author Commented:
Glenn,
The data will not be in the same cells. They could be anywhere in the sheet.
I'm not certain, but it appears that the above assumes that the "Continental U.S. Ground"  is in a specific cell?
Euro5Author Commented:
Not anywhere in the sheet - they could be in any ROW - the phrases are always in Column A.
Glenn RayExcel VBA DeveloperCommented:
So, "Continental U.S. Ground" (and it's merged/wrapped cells) could be on any row in column A - then followed further down the sheet by the table?

Could you post another variation of your example that shows other possible configuration(s)?  I'm sure it's an easy mod to the example code I provided.

-Glenn
aikimarkCommented:
Try this:
Sub Q_28686495()
    Dim rngFindH1 As Range
    Dim rngFindH2 As Range
    Set rngFindH1 = ActiveSheet.Range("A:C").Find("Continental U.S. Ground")
    Set rngFindH2 = ActiveSheet.Range("A:A").Find("Weight (in Lbs )", after:=rngFindH1.Cells(1, 1))
    ActiveSheet.Range(rngFindH1.MergeArea.Cells(1, 1).Offset(1), _
                        rngFindH2.Offset(-2)).EntireRow.Delete
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Wow, perfect! Thanks so much!
Euro5Author Commented:
What if there is an error? Can we handle that?
aikimarkCommented:
What kind of error?  Probably best to ask a new question and post a link to this question.
Euro5Author Commented:
aikimark - sorry, yes you are right. Asked New question is - ID: http:Q_28687555
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.