Dynamic Range Defined by Value of Cell

D

Domenick

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

Otto Moehrbach

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
 
T

T. Valko

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

Domenick

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?
 
D

Domenick

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

T. Valko

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

Otto Moehrbach

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

Domenick said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top