Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |