Union method for Range Object
This works for skipping columns
Should work for skipping rows or skipping both rows and columns. (as long
as the corners of the data are a rectangle and skipped rows and columns are
the same for all - so this would work:
Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7")
)
---------------------------
Dim X_in As Range, X As Variant
Dim i As Long, j As Long
Dim rngRow As Range, rngCol As Range
Dim rw As Range, col As Range
Dim ii As Long, jj As Long
Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6")
Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn)
Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow)
ReDim X(1 To rngRow.Count, 1 To rngCol.Count)
'Debug.Print rngRow.Address, rngCol.Address
For Each cell In X_in
i = 0
For Each rw In rngRow
i = i + 1
If rw.Row = cell.Row Then
ii = i
Exit For
End If
Next
j = 0
For Each col In rngCol
j = j + 1
If col.Column = cell.Column Then
jj = j
End If
Next
X(ii, jj) = cell.Value
Next cell
For i = 1 To UBound(X, 1)
For j = 1 To UBound(X, 2)
sStr = sStr & X(i, j) & ", "
Next
sStr = sStr & vbNewLine
Next
Debug.Print sStr
End Sub
--
Regards,
Tom Ogilvy
"Chad" wrote in message
...
I am attempting to fill an array with a non-contiguous range. I have
figured
out several ways to do this but each has its limitations. Using the
following, the range object created has a single column (even though the
reference includes multiple columns).
Dim X_in As Range, X As Variant, i, j
Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6)
X = X_in
For j = 1 To UBound(X, 2)
For i = 1 To UBound(X, 1)
Cells(i, j + 10) = X(i, j)
Next i
Next j
I have tried the Union method, areas, etc.
Thanks,
Chad
|