I'm not exactly certain what you want to do. I'm assuming you want to be
able to determine how large the range is for the list of fruits

Sub Test()

Dim myRange As Range
Dim r As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet
Set myRange = aWS.Range("A2") '<~~~replace with the address of the header.
Set myRange = myRange.Offset(1, 0)
lrow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row

Set myRange = myRange.Resize(lrow - myRange.Row + 1, 1)

'To loop through the range I use this
For Each r In myRange
Debug.Print r.Address, r.Value
Next r

End Sub
--
HTH,
Barb Reinhardt



"Chad" wrote:

Hi All

I have a problem perhaps someone can help with. I have searched for
quite some time and can not find anything.

There is a list of accounts which I want to send through to a template
one at a time. The format looks like this.

Header
Apple
Apple
Apple
Pear
Pear
Orange
Orange

I want to be able to isolate and copy one group at a time send it to
the template then move onto the next group. The list will change
constantly and I have no way to know if there will be one or many more
Apples, oranges etc on the trott.

Thanks

Chad