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.

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.

Take a look at this:
http://www.ozgrid.com/Excel/DynamicRanges.htm

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Domenick" wrote:

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.

How do you intend to use this range?

A1:INDEX(A:C,MATCH("Total",A:A,0),3)

That will define the range but by itself that really doesn't do anything.

--
Biff
Microsoft Excel MVP


"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.

I my macro, I currently have :

Range("A8:af105").Select
Selection.Copy

Which always selects the same number of rows. I want to change this to
select A8 through af"?" where "?" is the row number of the first row that has
"TOTAL" in column A.

Is that a little clearer?

"Otto Moehrbach" wrote:

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.

.

I my macro, I currently have :

Range("A8:af105").Select
Selection.Copy

Which always selects the same number of rows. I want to change this to
select A8 through af"?" where "?" is the row number of the first row that has
"TOTAL" in column A.

Is that a little clearer?


"T. Valko" wrote:

How do you intend to use this range?

A1:INDEX(A:C,MATCH("Total",A:A,0),3)

That will define the range but by itself that really doesn't do anything.

--
Biff
Microsoft Excel MVP


"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.


.

I'm not the best programmer but Otto's code will do what you want with a few
tweaks.

Dim rColA As Range
Dim TotalRng As Range
Set rColA = Range("A8", Range("A" & Rows.Count).End(xlUp))
Set TotalRng = rColA.Find(What:="TOTAL", LookAt:=xlWhole)
Set TotalRng = Range("A8", TotalRng.Offset(, 31))
TotalRng.Copy
Range("G2").Select 'change this to the paste destination
ActiveSheet.Paste
Application.CutCopyMode = False 'kills the "marching ants"

--
Biff
Microsoft Excel MVP


"Domenick" wrote in message
...
I my macro, I currently have :

Range("A8:af105").Select
Selection.Copy

Which always selects the same number of rows. I want to change this to
select A8 through af"?" where "?" is the row number of the first row that
has
"TOTAL" in column A.

Is that a little clearer?


"T. Valko" wrote:

How do you intend to use this range?

A1:INDEX(A:C,MATCH("Total",A:A,0),3)

That will define the range but by itself that really doesn't do anything.

--
Biff
Microsoft Excel MVP


"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.


.

Change the Offset(,2) to Offset(,31). Otto

"Domenick" wrote in message
...
I my macro, I currently have :

Range("A8:af105").Select
Selection.Copy

Which always selects the same number of rows. I want to change this to
select A8 through af"?" where "?" is the row number of the first row that
has
"TOTAL" in column A.

Is that a little clearer?

"Otto Moehrbach" wrote:

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.

.