MsgBox Range("H3:H14,L3:M14").Address(ReferenceStyle:=xlR 1C1) yields
-------------------------
R3C8:R14C8,R3C12:R14C13
-------------------------

but the VBA coding
---------------------------------------
Range(R3C8:R14C8, R3C12:R14C13).Select
---------------------------------------

doesn't even get past the parser - the line turns red and that's the end of it.


Intuitively,
------------------------------------------------------------------------------
With ActiveSheet
Range(.Cells(3, 8), .Cells(14, 8), .Cells(3, 12), .Cells(14, 13)).Select
End With
------------------------------------------------------------------------------
seems tb closer, but it dies at compile time with "Wrong number of arguments or
invalid property assignment".

Can anybody give me a nudge in the right direction?
--
PeteCresswell

Range() takes one or two arguments. If one, it has to be a recognizable
range address, if two it has to be references to two diametrically opposed
corners of the range. Range("R3C8:R14C8") didn't work for me, but you can
easily decompose RrCc into Cells(r,c). Then use Union to connect
discontiguous areas.

Try something like this:

Union(Range(cells(3,8),cells(14,8)), Range(cells(3,12),cells(14,13))).Select

which worked for me from the Immediate window.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"(PeteCresswell)" wrote in message
...

MsgBox Range("H3:H14,L3:M14").Address(ReferenceStyle:=xlR 1C1) yields
-------------------------
R3C8:R14C8,R3C12:R14C13
-------------------------

but the VBA coding
---------------------------------------
Range(R3C8:R14C8, R3C12:R14C13).Select
---------------------------------------

doesn't even get past the parser - the line turns red and that's the end
of it.


Intuitively,
------------------------------------------------------------------------------
With ActiveSheet
Range(.Cells(3, 8), .Cells(14, 8), .Cells(3, 12), .Cells(14,
13)).Select
End With
------------------------------------------------------------------------------
seems tb closer, but it dies at compile time with "Wrong number of
arguments or
invalid property assignment".

Can anybody give me a nudge in the right direction?
--
PeteCresswell

Per Jon Peltier:
Try something like this:

Union(Range(cells(3,8),cells(14,8)), Range(cells(3,12),cells(14,13))).Select

which worked for me from the Immediate window.

Bingo.... but I'm having a problem with getting it's size

The ultimate goal being to take the contents of a
discontinuous range from one sheet and plop it into
another sheet...


To Wit:
--------------------------------------------------
Sub aa()
Dim mySourceWS As Excel.Worksheet
Dim myTargetWS As Excel.Worksheet

Dim mySourceRange As Excel.Range
Dim myTargetRange As Excel.Range

Set mySourceWS = Application.Worksheets("Data-Collateral Manager")
Set myTargetWS = Application.Worksheets("Composite")


With mySourceWS
Set mySourceRange = Union(Range(.Cells(2, 8), .Cells(14, 8)),
Range(.Cells(2, 12), .Cells(14, 13)))
End With

PAUSE CODE HERE...
Set myTargetRange = myTargetWS.Range(myTargetWS.Cells(2, 15),
myTargetWS.Cells(2, 15))

Set myTargetRange = myTargetRange.Resize(mySourceRange.Rows.Count,
mySourceRange.Columns.Count)

mySourceRange.Value = myTargetRange.Value
End Sub
--------------------------------------------------
?mySourceRange.Rows.Count
13
?mySourceRange.Columns.Count
1
--------------------------------------------------

Seems like .Rows.Count only accounts for the first part of the union.

--
PeteCresswell

..Rows.Count and .Columns.Count only look at the first area of a
discontiguous range. You can do a loop:

For each rngArea in mySourceRange.Areas

but since you have a 2-D array of areas in your range, this will overcount
rows and columns. What I've done in these cases is something like this
pseudocode

Set myRows = mySourceData.EntireRow
iRowCount = 0
For Each rngArea In myRows.Areas
iRowCount = iRowCount + rngArea.Rows.Count
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"(PeteCresswell)" wrote in message
...
Per Jon Peltier:
Try something like this:

Union(Range(cells(3,8),cells(14,8)),
Range(cells(3,12),cells(14,13))).Select

which worked for me from the Immediate window.

Bingo.... but I'm having a problem with getting it's size

The ultimate goal being to take the contents of a
discontinuous range from one sheet and plop it into
another sheet...


To Wit:
--------------------------------------------------
Sub aa()
Dim mySourceWS As Excel.Worksheet
Dim myTargetWS As Excel.Worksheet

Dim mySourceRange As Excel.Range
Dim myTargetRange As Excel.Range

Set mySourceWS = Application.Worksheets("Data-Collateral Manager")
Set myTargetWS = Application.Worksheets("Composite")


With mySourceWS
Set mySourceRange = Union(Range(.Cells(2, 8), .Cells(14, 8)),
Range(.Cells(2, 12), .Cells(14, 13)))
End With

PAUSE CODE HERE...
Set myTargetRange = myTargetWS.Range(myTargetWS.Cells(2, 15),
myTargetWS.Cells(2, 15))

Set myTargetRange = myTargetRange.Resize(mySourceRange.Rows.Count,
mySourceRange.Columns.Count)

mySourceRange.Value = myTargetRange.Value
End Sub
--------------------------------------------------
?mySourceRange.Rows.Count
13
?mySourceRange.Columns.Count
1
--------------------------------------------------

Seems like .Rows.Count only accounts for the first part of the union.

--
PeteCresswell

Per Jon Peltier:
.Rows.Count and .Columns.Count only look at the first area of a
discontiguous range. You can do a loop:

I think I'm trying to fool Mother Nature on this one.... with the discontinuous
range.

Seems like a more direct approach would be for me to just pick apart the larger
range cell-by-cell and grab what I need.

But that means that I'd need to store that range address somewhere in the chart
object. The series behind the chart won't do because it's just a subset of the
larger range. But when I create the chart, I know what the larger range is.

Tried .Chart.Name (after massaging the range, replacing ":" with "-") but it
didn't take.

Looked for something like .Tag, but didn't find it.

Can you think of some chart or shape property that I could use to squirrel away
a range address like "R3C02:R25C17"?

Even if I could make .Name stick, it would seem a dicey in that two charts with
the same macro data range would break my little scheme.
--
PeteCresswell

In line -

"(PeteCresswell)" wrote in message
...
Per Jon Peltier:
.Rows.Count and .Columns.Count only look at the first area of a
discontiguous range. You can do a loop:

I think I'm trying to fool Mother Nature on this one.... with the
discontinuous
range.

Seems like a more direct approach would be for me to just pick apart the
larger
range cell-by-cell and grab what I need.

Why doesn't Jon's advice work for you.

But that means that I'd need to store that range address somewhere in the
chart object.

Isn't the range defined and hence stored in the series formula(s)

The series behind the chart won't do because it's just a subset of the
larger range. But when I create the chart, I know what the larger range
is.

Tried .Chart.Name (after massaging the range, replacing ":" with "-") but
it
didn't take.

Chart names are more restricted than shape names in terms of allowable
punctuation characters. Replace ":" in the formula with something,
underscore is good.

Looked for something like .Tag, but didn't find it.

There's no tag or Alternative text property. Some text box perhaps, made not
visible.

Can you think of some chart or shape property that I could use to squirrel
away
a range address like "R3C02:R25C17"?

Why R1C1 and not A1, look at app.ConvertFormula. What happens if the
harcoded range is moved !


Even if I could make .Name stick, it would seem a dicey in that two charts
with
the same macro data range would break my little scheme.
--
PeteCresswell

Regards,
Peter T

Looked for something like .Tag, but didn't find it.

There's no tag or Alternative text property. Some text box perhaps, made
not
visible.

I suggested this (and provided a code sample) in Pete's more recent thread
which asked about .Tag.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Per Peter T:
Seems like a more direct approach would be for me to just pick apart the
larger
range cell-by-cell and grab what I need.

Why doesn't Jon's advice work for you.

Dunno - but the first thing that comes to mind is RCI on my part...


But that means that I'd need to store that range address somewhere in the
chart object.

Isn't the range defined and hence stored in the series formula(s)

Yes. But the range I need is different from the chart's. It's a superset
of the chart range from which I'd extract some rows/columns to place
below the chart.


Chart names are more restricted than shape names in terms of allowable
punctuation characters. Replace ":" in the formula with something,
underscore is good.

I tried a dash... but no go. I'll give the underscore a shot...

But now I'm re-thinking my whole strategy. This started because I'm creating
many chart objects in one process and arranging them in another process.

I think I need to combine the two processes so that I position/size each chart
when I create it. At that time I have access to the data I need so I should be
able to also place the supplemental data beneath the chart at that time.
--
PeteCresswell