VBA macro to remove rows not working

In the attached,

Net Rate Sheet 2.DeleteThem should remove anything on Net Rate sheet 2 between the service name and the phrases
                If .Cells(lngend, 1) = "Please refer to list rates for this service." Or _
                   .Cells(lngend, 1) = "Net Rates are not available for these services" Then

However, it is not working for the service "Domestic First Overnight Non-Freight"

Can someone help?
deletethemissue.xlsm
Euro5Asked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
It's because it's looking for an ending phrase and a rate table between the two and it doesn't find one.  Note that in the following code (on both sheets) you are looking for the same phrase twice. One should be removed or changed.

                ' Look for special ending phrases
                If .Cells(lngend, 1) = "Please refer to list rates for this service." Or _
                   .Cells(lngend, 1) = "Please refer to list rates for this service." Or _
                   .Cells(lngend, 1) = "Net Rates are not available for these services" Then
                    Exit For
                End If

Open in new window

0

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:
Yes, you are right about the duplicate - I was trying to see if there was a variance in the phrase that I wasn't catching.

What it used to do was:
1. look for phrase - example ""Domestic First Overnight Non-Freight"
2. look for phrase either "Please refer to list rates..." or "Net Rates are not available..."
3. if that phrase was below, it would remove everything from the first phrase to the last

In these instances there would NOT be a rate table between the two phrases.
I know this used to work this way....    can you help?
0
Euro5Author Commented:
I removed duplicate and used a trim. Working perfectly again. Thanks!
0
Martin LissOlder than dirtCommented:
You're welcome.
0
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.