VBA Delete row based on phrase

I have this code to delete rows based on phrase.
I need one blank row between the two phrases"Continental U.S. Ground" & "Weight (in Lbs ).
What I have found is that it will run correctly the first time - but after that it will actually delete the row with
"Continental U.S. Ground" phrase in it.

Could we add something to count the rows first, to make sure that we always have one blank row between?
Also, what if the phrase can't be found? An error code?

Thanks!!

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


Prior related question: http:Q_28686495.html
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:
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))
If Not rngFindH2 Is Nothing Then
    ActiveSheet.Range(rngFindH1.MergeArea.Cells(1, 1).Offset(1), _
                        rngFindH2.Offset(-2)).EntireRow.Delete
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
If that doesn't work then change line 6 to If Not rngFindH1 Is Nothing Then
0
Euro5Author Commented:
Martin,
It still deletes the "Continental U.S. Ground" if I run it a second time.
Meaning, if the two lines are already one row apart, I need it to not do anything.
Only delete rows if there additional rows between.
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.

aikimarkCommented:
Do you need to handle conditions where either/both strings are not present as well as their proximity (<=2 lines)?

What if they are on adjacent rows?  Do you need to insert rows?
0
Euro5Author Commented:
aikimark,
The condition to handle is what if the phrase is not present? Just move on with the code.

Otherwise, yes, if they are adjacent, I would need to insert one row.
There must always be one row between.
0
aikimarkCommented:
This version ensures that the strings exist:
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))
    If (rngFindH1 Is Nothing) Or (rngFindH2 Is Nothing) Then
    Else
        ActiveSheet.Range(rngFindH1.MergeArea.Cells(1, 1).Offset(1), _
                        rngFindH2.Offset(-2)).EntireRow.Delete
    End If
End Sub

Open in new window

0
Euro5Author Commented:
ailimark,
I don't know....the same thing is happening.

When I run the first time, it works - puts only one row between Continental U.S. Ground & Weight (in Lbs ).
When I run the second time, it removes the line Continental U.S. Ground
When I run the third time it gives error Object variable or with block variable not set
0
aikimarkCommented:
This seems to do what you've described
Sub Q_28686495()
    Dim rngFindH1 As Range
    Dim rngFindH2 As Range
    Set rngFindH1 = ActiveSheet.Range("A:C").Find("Continental U.S. Ground")
    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

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
aikimarkCommented:
I realized when I went to add the additional test for row separation that the second find method should be protected against a no-find result of the first find method.
0
Euro5Author Commented:
Thank 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.