ranges

G

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
 
R

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

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
 
R

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

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
 
B

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
 
R

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

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

Similar Threads

Save Array in memory 5
sum a range in VBA 4
Change CellA. 2
Test for multiple rows 6
commandbutton does not answer 0
Grab the right please 1
Sort and align 2 ranges 1
Compare and Highlight Differences 5

Top