vba identify phrase and remove rows

I need vba to
1. Identify phrase Domestic Priority Overnight in column A (could be any row)
2. IF 4 rows below is phrase "Please refer to list rates for this service" - in column A
3. Delete all rows from first phrase to the second
   Domestic Priority Overnight to Please refer to list rates for this service
4. If not, continue with code
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.

Martin LissOlder than dirtCommented:
Sub DeleteThem()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
    If Cells(lngRow, 1) = "Domestic Priority Overnight" Then
        If Cells(lngRow, 1).Offset(4, 0) = "Please refer to list rates for this service" Then
            Range(Cells(lngRow, 1), Cells(lngRow, 1).Offset(4, 1)).EntireRow.Delete
        End If
    End If
Next
End Sub

Open in new window

Martin LissOlder than dirtCommented:
just noticed that step 3 says
3. Delete all rows from first phrase to the second
   Domestic Priority Overnight to Please refer to list rates for this service
Do you really mean the second one or the first one below the "Domestic Priority Overnight"?
Euro5Author Commented:
I mean from one phrase to the next. From the first phrase to the second. Both phrases.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Martin LissOlder than dirtCommented:
Ok then if this is the situation then my code will delete rows 5 through 9 inclusive.
delete
Euro5Author Commented:
Just realized this wont work because it is not a list.
It is random on the sheet - will be in Column A but could be any row.
There are many blanks on the Column A.
Martin LissOlder than dirtCommented:
Are you saying that both phrases can be in any column?
Martin LissOlder than dirtCommented:
This will deal with the phrases being in any column.

Sub DeleteThem()
Dim lngLastRow As Long
Dim lngRow As Long
Dim rngDPO As Range
Dim rngPRT As Range

Application.ScreenUpdating = False
lngLastRow = ActiveSheet.UsedRange.Rows.Count

For lngRow = lngLastRow To 1 Step -1
    Rows(lngRow).Select
    With Selection
        Set rngDPO = .Find(What:="Domestic Priority Overnight", After:=ActiveCell, _
                    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With
    If Not rngDPO Is Nothing Then
        Rows(lngRow + 4).Select
        With Selection
            Set rngPRT = .Find(What:="Please refer to list rates for this service", After:=ActiveCell, _
                    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not rngPRT Is Nothing Then
                Range(Cells(lngRow, 1), Cells(lngRow, 1).Offset(4, 1)).EntireRow.Delete
            End If
        End With
    End If
Next
Application.ScreenUpdating = True

End Sub

Open in new window

Euro5Author Commented:
No - the phrases are always in column A. I just don't know the row.
I am attaching the sheet...
If these are not removed, the code will try to create named ranges and it will be a problem.

samplenetsheetexample.xlsx
Martin LissOlder than dirtCommented:
Try my code in post ID: http:#a40820927 and tell me what's wrong if anything.
Euro5Author Commented:
Martin, Doesn't do anything.
I think I need more help here. How would you suggest that I remove the service names in each instance if they say "Please refer to list rates for service"?

I need to find them and remove them or the calculations later on will think they are accurate named ranges and give bad results.snapnetsheetexample.xlsx
Martin LissOlder than dirtCommented:
In your original question you say
1. Identify phrase Domestic Priority Overnight in column A (could be any row)
2. IF 4 rows below is phrase "Please refer to list rates for this service" - in column A
however in the most recent workbook there is only one "Domestic Priority Overnight" and that is in row 31 and while there are several "Please refer to list rates for this service." the closest one is in row 29 which is 2 rows above. So please tell me by row numbers which rows you want to delete.
Euro5Author Commented:
If the service has a service, it will follow as this example:PO
If not it will follow this example:FO
It is the second that I am concerned about - where there are no rates following the service heading.
Martin LissOlder than dirtCommented:
As I asked above, can you tell me which rows by row number I should delete in the workbook from post ID: 40822453?
Euro5Author Commented:
Rows: 26,27,28,29
Rows: 3414,3415,3416,3417,3418,3419,3420,3421
Rows: 3625,3626,3627,3628,3629
Martin LissOlder than dirtCommented:
I'm sorry but the rows you mention do not all involve "Domestic Priority Overnight" and "Please refer to list rates for this service" and none of them are 4 rows apart as you described, so please try and tell me why those 3 sets of rows need to be deleted.

Are those the only three groups that need to be deleted?
Euro5Author Commented:
These are the only three groups that need to be deleted.
I realized after asking my first question, that the rows on all sheets are NOT 4 rows apart as I had first thought.
So I need a better solution.
In this sheet the rows that need removed are Domestic First-Overnight Non-Freight, Continental US Home Delivery, & Ground - Ground Multiweight Rates
Martin LissOlder than dirtCommented:
OK I think we're getting someplace now but I'm not sure when to delete them. It looks like they should they be deleted if there are no rates between those 3 (Domestic First-Overnight Non-Freight, Continental US Home Delivery, & Ground - Ground Multiweight Rates) and some phrase below them like "Please refer to list rates for this service." and "Net Rates are not available for these services" .  If that's the case then can you supply a complete list of those phrases?  I also see that one of them ends in a period while the other doesn't so I need to ask if the phrases are manually typed in or are they chosen from a list of some sort?
Euro5Author Commented:
That is correct - when there are no rates present for a service and one of those phrases are used.
They are not typed in - they are always produced from the system.
I went through many sheets and these are the two exact phrases - one with period, one without.
Could be EITHER.

Please refer to list rates for this service.      
Net Rates are not available for these services
Martin LissOlder than dirtCommented:
Try this

Sub DeleteThem()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngEnd As Long
Dim bOK As Boolean

Application.ScreenUpdating = False

With ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    
    For lngRow = lngLastRow To 1 Step -1
        If .Cells(lngRow, 1) = "Domestic First Overnight Non-Freight" Or _
           .Cells(lngRow, 1) = "Continental US Home Delivery" Or _
           .Cells(lngRow, 1) = "Ground - Ground Multiweight Rates" Then
            bOK = False
            For lngEnd = lngRow + 1 To lngLastRow
                ' Look for special ending 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
                    Exit For
                End If
                ' Look for a rate table.
                If InStr(1, .Cells(lngEnd, 1), "Weight") > 0 Then
                    ' We found one so quit this search
                    bOK = True
                    Exit For
                End If
            Next
            If Not bOK Then
                Range(.Cells(lngRow, 1), .Cells(lngEnd, 1)).EntireRow.Delete
            End If
        End If
    Next
End With

Application.ScreenUpdating = True
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:
That worked! Thanks again!
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
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.