VBA remove or insert rows by phrase

I don't get an error but the the following returns 1 which you don't account for.

rngFindH2.Row - rngFindH1.MergeArea.Cells(1, 1).Offset(1).Row

What do you want to do in this case?

You can use the following code and it will do what you are looking for..

Sub RMGRNet2()
    Application.ScreenUpdating = False
    Net_Rates_2.Activate

    Dim rngFindH1 As range
    Dim rngFindH2 As range
    Set rngFindH1 = ActiveSheet.range("A:A").Find("Package Type(s): RMGR")
    If Not (rngFindH1 Is Nothing) Then Set rngFindH2 = ActiveSheet.range("A:A").Find("Weight (in Lbs )", after:=rngFindH1)


    Dim lr As Long

    If Not (rngFindH1 Is Nothing) And Not (rngFindH2 Is Nothing) Then
        lr = rngFindH2.Row - rngFindH1.Row

        If lr > 2 Then
            Rows(rngFindH1.Row + 1 & ":" & rngFindH1.Row + lr - 2).Delete
        ElseIf lr = 1 Then
            rngFindH2.EntireRow.Insert

        End If
    End If


End Sub

Select allOpen in new window

Saurabh...

@Martin - it would do nothing with =1.
That would mean that there was one blank space between the header and the table.

@Saurabh - I tried your code, and when I choose Net Rate Sheet 2 and run the macro it works perfectly.
But when I run with all the other code - pushing the button Re-Rate on Enter Data sheet, the run stops at this code. Can you help me identify why??
Module 6 Call_Net_Rates_2.RMGRNet2
Rerate-v5.3-sample-RMGR.xlsm

You are getting the error because the range you want to move to the bottom is already at the bottom. This will fix that. (I added rows 17 and 20)

Sub MoveRMGR2()
Dim lngRow As Long

Dim rngRMGR As range
Net_Rates_2.Activate
With ActiveSheet
    Set rngRMGR = .Cells.Find(What:="RMGR", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
    If Not rngRMGR Is Nothing Then
        For lngRow = rngRMGR.Row + 2 To .UsedRange.Rows.Count
            If .Cells(lngRow, 1) = "" Then
                lngRow = lngRow - 1
                Exit For
            End If
        Next
    End If
    
    If .UsedRange.Rows.Count + 1 <> lngRow Then
        .Rows(rngRMGR.Row & ":" & lngRow).Cut
        .Cells(.UsedRange.Rows.Count + 1, 1).EntireRow.Insert
    End If
End With

End Sub

Select allOpen in new window

@Martin - what if there is no PRP or RMGR? can we have it exit?

Do you mean exit the MoveRMGR2 and MovePRP subs? If so It will. If you mean you want to exit the RunSeries sub then I have other questions.

I'm just seeing that when there is a PRP or RMGR in the sheet, it does work. But in the other sheet there is no PRP or RMGR present in the sheet, I get the error
Object variable or with block variable not set.

Each set of Net Rate sheets may contain any combination of services including PRP or RMGR or not.

Thank you!!