Discontinuous Range via VBA using R1C1?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

MsgBox Range("H3:H14,L3:M14").Address(ReferenceStyle:=xlR1C1) 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,
 
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
 
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.
Click to expand...

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
 
..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
 
'following function combines number of cells which are stored as array in given row


Function CombineDiscontinuosCellsInRow(RowNo, Coll() As Integer) As Range
'combines discontinuouscellsInRow
Dim Rn As Range
Dim i As Integer
Set Rn = Cells(RowNo, Coll(1))
For i = 2 To UBound(Coll)
Set Rn = Union(Rn, Cells(RowNo, Coll(i)))
Next
Set CombineDiscontinuosCellsInRow = Rn
End Function

Sub Test_CombineDiscontinuosCellsInRow()
Dim R As Range
Dim c(3) As Integer
c(1) = 1
c(2) = 5
c(3) = 7
Set R = CombineDiscontinuosCellsInRow(5, c)
R.Select
End Sub
 
Back
Top