Hi Pele,

You could use the Find method to do this:

Dim rng As Range

Set rng = Columns(3).Find(What:="total")
If Not rng Is Nothing Then MsgBox rng.Row

Set rng = Nothing

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Pele wrote:
I have a data gathering template used in our department and the
length of the template could vary. Regardless of the length though,
the row at the end of the template will have the word TOTAL in column
C of that row.

The USer can still add text into rows below the one identified as the
end of the template i.e. if cell C69 has the word, Total, then row 69
will be assumed to be the ned of the template regardless of how many
non-empty rows are after that.

I tried to write a code to identify the end of the template(see
below) but it is not working. The Do Loop wasn't exited. ANy help
will be appreciated.

Pele


Worksheets(2).Activate
s = 0
Do
s = s + 1
Cells(s, 3).Select
z = Cells(s, 3)
If z = "total" Then
Exit Do
End If
Loop
Size = z