VBA modify code to delete phrase and table

I need the code below modified to find the phrase "Package Type(s):PRP" in column A of sheet.
IF there is a table below it - "Weight (in Lbs )" - delete the phrase and the entire table.

Can anyone help?

Sub PRPNet1()
Application.ScreenUpdating = False
    Net_Rates_1.Activate
    
    Dim rngFindH1 As range
    Dim rngFindH2 As range
    Set rngFindH1 = ActiveSheet.range("A:C").Find("Package Type(s):PRP")
    If (rngFindH1 Is Nothing) Then
    Else
        Set rngFindH2 = ActiveSheet.range("A:A").Find("Weight (in Lbs )", After:=rngFindH1.Cells(1, 1))
    End If
    If (rngFindH1 Is Nothing) Or (rngFindH2 Is Nothing) Then
    Else
        Select Case rngFindH2.Row - rngFindH1.MergeArea.Cells(1, 1).Offset(1).Row
            Case Is >= 2
                ActiveSheet.range(rngFindH1.MergeArea.Cells(1, 1).Offset(1), _
                        rngFindH2.Offset(-2)).EntireRow.Delete
            Case 0
                rngFindH2.EntireRow.Insert
            Case Else
        End Select
    End If
End Sub

Open in new window

Reratev4sample.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:
So you no longer care about the ending phrase? And is it only the header named "Package Type(s):PRP" that you are interested in?
0
Martin LissOlder than dirtCommented:
And is it only for Net_Rates_1 or do you want to do Net_Rates_2 at the same time?
0
Euro5Author Commented:
I ONLY need to delete "Package Type(s):PRP" type. - it is complicating everything. I need to get it out of there.
Can you get it out of there in both cases?
With the end phrase OR with the table ("Weight").
I would really appreciate that!
also, only need it work with Net 1 - I will add to Net 2.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Martin LissOlder than dirtCommented:
If I find a  "Package Type(s):PRP" how do I know where to end the delete? In other words the must be some kind of indicator below the  "Package Type(s):PRP" row that tells me the end of the range that I want to delete. What would the vales of that indicator be?
0
Euro5Author Commented:
I understand.
If it finds the "Weight (in Lbs )" - (this is the top row of table, column a)
Delete from the  "Package Type(s):PRP"
down the entire table.

If it finds "Please refer to list rates for this service." or  "Net Rates are not available for these services"

Then delete from the   "Package Type(s):PRP"  through the second phrase.
0
Martin LissOlder than dirtCommented:
Let me explain what I'm doing. I'm reading the rows backwards from the bottom (you pretty much have to do that when you are deleting rows) looking for "Package Type(s):PRP". When I find one I read the rows downward looking for something that tells me where to end the delete. So let's say I find "Weight (in Lbs )". I assume that I should still keep looking for "Please refer to list rates for this service." or  "Net Rates are not available for these services" because otherwise those wouldn't be deleted, but those two might be associated with a completely different header, and if they were I would delete too much.

So please, if you can, describe a hard and fast set of rules that will tell me where to stop.
0
Euro5Author Commented:
Sorry, I'm trying.

If it finds "weight in lbs", can it do 'all the way right & all the way down' to select the entire table?
Then delete everything from  "Package Type(s):PRP" to the bottom of the selected table? (snippy below)
It should not keep looking for phrases - "Please refer to list rates for this service." or  "Net Rates are not available for these services"

Those are only there (and should be deleted) if there is no table present. They are phrases basically indicating why there is no table.

prp
0
Martin LissOlder than dirtCommented:
OK now I understand. I'll have a solution for you in the morning.
0
Euro5Author Commented:
If it finds weight, select down to '150' and delete all rows.
0
Martin LissOlder than dirtCommented:
Try this which only checks sheet Net_Rates_1

Sub PRPNet1()
Application.ScreenUpdating = False

Dim lngLastRow As Long
Dim lngRow As Long
Dim lngLastPhrase As Long
Dim lngLastTable As Long

Application.ScreenUpdating = False

Net_Rates_1.Activate
    
With ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    lngPrevHdr = lngLastRow
    
    For lngRow = lngLastRow To 1 Step -1
        ' Look backwards and record where an ending phrase and the end of a table are found.
        ' This will be used to denote the last row to be deleted when and if a "Package Type(s):PRP"
        ' header is found. Note that if both the phrase and the table are found then ignore the
        ' phrase because phrases basically indicate why there is no table, so it must be
        ' associated with a previous table.
        If .Cells(lngRow, 1) = "Please refer to list rates for this service." Or _
           .Cells(lngRow, 1) = "Net Rates are not available for these services" Then
            lngLastPhrase = lngRow
        End If
        ' The last row of evey table contains "150"
        If .Cells(lngRow, 1) = "150" Then
            lngLastTable = lngRow
        End If
        
        If .Cells(lngRow, 1) = "Package Type(s): PRP" Then
            If lngLastTable > 0 Then
                Range(.Cells(lngRow, 1), .Cells(lngLastTable, 1)).EntireRow.Delete
            Else
                Range(.Cells(lngRow, 1), .Cells(lngLastPhrase, 1)).EntireRow.Delete
            End If
            lngLastTable = 0
            lngLastPhrase = 0
        End If
    Next
End With
End Sub

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:
God bless you. I was having anxiety attacks. As usual, works perfectly.
0
Martin LissOlder than dirtCommented:
As usual, works perfectly.
Well not always:) but I'm glad it works for you.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.