ranges
 

Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks

ranges
 

Describe the "on the fly" part of your message; that is, how is the A4 and
G7 cell limits being obtained? I ask because I would think you could just
use something like this...

Start = "A4" {by whatever means you use to obtain it}
Finish = "G7" {by whatever means you use to obtain it}
Set rSubRange = Range(Start & ":" & Finish)

--
Rick (MVP - Excel)


"greg" wrote in message
...
Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks

ranges
 

Well, as I look at the data in the first range. By values I need to grab a
second range.
Don't you actually use the first range as the starting point? to create a
second range.
So let me switch the first range to A20 to g40
and I want to grab
A30 to G 35.
Do you use 20 as the first row? And grab 10 to 15? or something like that?

Hope i am explaining this ok





"Rick Rothstein" wrote in message
...
Describe the "on the fly" part of your message; that is, how is the A4 and
G7 cell limits being obtained? I ask because I would think you could just
use something like this...

Start = "A4" {by whatever means you use to obtain it}
Finish = "G7" {by whatever means you use to obtain it}
Set rSubRange = Range(Start & ":" & Finish)

--
Rick (MVP - Excel)


"greg" wrote in message
...
Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks

ranges
 

My question is... How do you *know* it is A30 to B35 that you want? How does
that get specified? Are you selecting it? Typing the range into an InputBox?
Is it fixed at A30 and G35? Some other way?

Also, are we talking about a real Data/Table (specified from Excel's menu
bar) or just a rectangle of cells on the spreadsheet?

--
Rick (MVP - Excel)


"greg" wrote in message
...
Well, as I look at the data in the first range. By values I need to grab
a second range.
Don't you actually use the first range as the starting point? to create a
second range.
So let me switch the first range to A20 to g40
and I want to grab
A30 to G 35.
Do you use 20 as the first row? And grab 10 to 15? or something like
that?

Hope i am explaining this ok





"Rick Rothstein" wrote in message
...
Describe the "on the fly" part of your message; that is, how is the A4
and G7 cell limits being obtained? I ask because I would think you could
just use something like this...

Start = "A4" {by whatever means you use to obtain it}
Finish = "G7" {by whatever means you use to obtain it}
Set rSubRange = Range(Start & ":" & Finish)

--
Rick (MVP - Excel)


"greg" wrote in message
...
Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks

ranges
 

thanks,
The first range is a named range.
So I
set rRange = Range("MyTable")
Then I start reading the values in the A column
At some time there will be a section of specific values.
At that point I would like to create a new range on the fly



"Rick Rothstein" wrote in message
...
My question is... How do you *know* it is A30 to B35 that you want? How
does that get specified? Are you selecting it? Typing the range into an
InputBox? Is it fixed at A30 and G35? Some other way?

Also, are we talking about a real Data/Table (specified from Excel's menu
bar) or just a rectangle of cells on the spreadsheet?

--
Rick (MVP - Excel)


"greg" wrote in message
...
Well, as I look at the data in the first range. By values I need to grab
a second range.
Don't you actually use the first range as the starting point? to create
a second range.
So let me switch the first range to A20 to g40
and I want to grab
A30 to G 35.
Do you use 20 as the first row? And grab 10 to 15? or something like
that?

Hope i am explaining this ok





"Rick Rothstein" wrote in message
...
Describe the "on the fly" part of your message; that is, how is the A4
and G7 cell limits being obtained? I ask because I would think you could
just use something like this...

Start = "A4" {by whatever means you use to obtain it}
Finish = "G7" {by whatever means you use to obtain it}
Set rSubRange = Range(Start & ":" & Finish)

--
Rick (MVP - Excel)


"greg" wrote in message
...
Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as
MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks

ranges
 

Hello Greg,

Function grabsubrange(r As Range) As Range
Dim r1 As Long, r2 As Long, c1 As Long, c2 As Long
r1 = Int(Rnd() * r.Rows.Count + 1)
r2 = Int(Rnd() * r.Rows.Count + 1)
c1 = Int(Rnd() * r.Columns.Count + 1)
c2 = Int(Rnd() * r.Columns.Count + 1)
With Application.WorksheetFunction
Set grabsubrange = Range(r.Cells(.Min(r1, r2), .Min(c1, c2)),
_
r.Cells(.Max(r1, r2), .Max(c1, c2)))
End With
End Function

Sub test1()
Dim v As Variant
For Each v In grabsubrange(Range("A1:F20"))
v.Formula = "grabbed"
Next v
End Sub

Regards,
Bernd

ranges
 

Specific values? Plural (as in more than one differing value)? Will these
values be in a contiguous block or in non-contiguous cells? HOW will you be
letting the macro know what these values are? By selecting them? By
answering an InputBox question? Getting them from another cell in the
worksheet? Or will these values always be the same all the time (and if so,
what are these values then)?

--
Rick (MVP - Excel)


"greg" wrote in message
...
thanks,
The first range is a named range.
So I
set rRange = Range("MyTable")
Then I start reading the values in the A column
At some time there will be a section of specific values.
At that point I would like to create a new range on the fly



"Rick Rothstein" wrote in message
...
My question is... How do you *know* it is A30 to B35 that you want? How
does that get specified? Are you selecting it? Typing the range into an
InputBox? Is it fixed at A30 and G35? Some other way?

Also, are we talking about a real Data/Table (specified from Excel's menu
bar) or just a rectangle of cells on the spreadsheet?

--
Rick (MVP - Excel)


"greg" wrote in message
...
Well, as I look at the data in the first range. By values I need to
grab a second range.
Don't you actually use the first range as the starting point? to create
a second range.
So let me switch the first range to A20 to g40
and I want to grab
A30 to G 35.
Do you use 20 as the first row? And grab 10 to 15? or something like
that?

Hope i am explaining this ok





"Rick Rothstein" wrote in message
...
Describe the "on the fly" part of your message; that is, how is the A4
and G7 cell limits being obtained? I ask because I would think you
could just use something like this...

Start = "A4" {by whatever means you use to obtain it}
Finish = "G7" {by whatever means you use to obtain it}
Set rSubRange = Range(Start & ":" & Finish)

--
Rick (MVP - Excel)


"greg" wrote in message
...
Hello,
Suppose I have a table in excel. A1 to 12 G. It is defined as
MyTable.

now I want to programatically grab or define a sub range.
Say A4 to G7.

How can this be done?
If I have
Dim rRange as Range
Dim rRange2 as Range
set rRange = Range("MyTable")

How can I get the second range, from the first, on the fly?

thanks