I don't know what you mean by "define a range" but this macro will provide
the address of the range. HTH Otto
Sub FindTOTAL()
Dim rColA As Range
Dim TotalRng As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set TotalRng = rColA.Find(What:="TOTAL", LookAt:=xlWhole)
Set TotalRng = Range("A2", TotalRng.Offset(, 2))
MsgBox TotalRng.Address
End Sub
"Domenick" wrote in message
...
I am just getting started with dynamic ranges. I have data as follows:

A B C
Product1 qty cost
Product2 qty cost
...
ProductN qty cost
TOTAL qtytot costtot

The number of products varies. There is other data below this that is
unrelated. How can I define a range dynamically that will always capture
A:C
and as many rows up and including the first time it finds "TOTAL" in
column A?

Any help greatly appreciated.

Thank you.