..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