Sub Rangeheaders_test()

 Dim r As Long
 Dim endRow As Long
 Dim endCell As Range
 Dim headers As Range

  With ActiveSheet

    endRow = 500
 For r = 1 To endRow

    If .Cells(r, "A").Value = "Period" Then
       Set endCell = .Rows(r).Find(What:="Agent Split", LookIn:=xlValues,_
    LookAt:=xlPart, After:=.Cells(r, "A"))

       Set headers = .Range(.Cells(r, "A"), endCell)
    End If
  Next r
   End With

    headers.Select

End Sub

With ActiveSheet
   endRow = 500

  r = 1 To endRow

    If .Cells(r, "A").Value = "Period" Then
       Set endCell = .Rows(r).Find(What:="Agent Split", LookIn:=xlValues,_
    LookAt:=xlPart, After:=.Cells(r, "A"))

       Set headers = .Range(.Cells(r, "A"), endCell)
    End If
  End With

    headers.Select