ranges

[greg]

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

 

[Rick Rothstein]

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)

 

[greg]

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]

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?

 

[greg]

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

 

[Bernd P]

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

 

[Rick Rothstein]

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