Union method for Range Object
 

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

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

Union method for Range Object
 

Thanks, Tom.

I am getting the range reference from a userform and the worksheet name is
included with the reference. I am currently using string functions to
extract the relevant reference. I was hoping there was a more elegant
solution, but it seems that there is not.

Chad




"Tom Ogilvy" wrote:

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

Union method for Range Object
 

More elegant than what? What do you mean be string functions to extract the
relevant reference? You didn't ask anything like that in your original
post? What is the actual question.

Guess that was a waste of time.

--
Regards,
Tom Ogilvy


"Chad" wrote in message
...
Thanks, Tom.

I am getting the range reference from a userform and the worksheet name is
included with the reference. I am currently using string functions to
extract the relevant reference. I was hoping there was a more elegant
solution, but it seems that there is not.

Chad




"Tom Ogilvy" wrote:

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

Union method for Range Object
 

I was looking for a way that did not involve a loop, etc. I thought perhaps
there was a simple solution that I was overlooking. I appreciate your answer
and it was not my intention to insult you by implying that your solution is
not elegant. By elegant, I mean simple. Perhaps your solution is as elegant
as there can be--I don't know.

You don't need to be so irascible.

chad



"Tom Ogilvy" wrote:

More elegant than what? What do you mean be string functions to extract the
relevant reference? You didn't ask anything like that in your original
post? What is the actual question.

Guess that was a waste of time.

--
Regards,
Tom Ogilvy


"Chad" wrote in message
...
Thanks, Tom.

I am getting the range reference from a userform and the worksheet name is
included with the reference. I am currently using string functions to
extract the relevant reference. I was hoping there was a more elegant
solution, but it seems that there is not.

Chad




"Tom Ogilvy" wrote:

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

Union method for Range Object
 

Nor do you need to be accusatory. It certainly is presumptuous for you to
assign anger to my response. Nothing farther from the truth. I was simply
trying to find out what your actual question was. Your response seemed to
have little relation to the original question - at least as I perceived
them.

original question:
I am attempting to fill an array with a non-contiguous range
followed by a statement saying you knew how to do it followed by code that
didn't get the job done and didn't say anything about not looping.

I your responser to where I provided code that filled the array properly you
said:

I am getting the range reference from a userform and the worksheet name is
included with the reference. I am currently using string functions to
extract the relevant reference.

you then made a conclusion:
I was hoping there was a more elegant solution, but it seems that there is
not.


Which to me doesn't seem relevant, but maybe its me. And yes, I did ask
more elegant than what (after all, the topic seemded to have changed) - as
it wasn't apparent what you meant - I certainly didn't take it as being
aimed at me. so pardon me for asking.

--
Regards,
Tom Ogilvy




"Chad" wrote in message
...
I was looking for a way that did not involve a loop, etc. I thought
perhaps
there was a simple solution that I was overlooking. I appreciate your
answer
and it was not my intention to insult you by implying that your solution
is
not elegant. By elegant, I mean simple. Perhaps your solution is as
elegant
as there can be--I don't know.

You don't need to be so irascible.

chad



"Tom Ogilvy" wrote:

More elegant than what? What do you mean be string functions to extract
the
relevant reference? You didn't ask anything like that in your original
post? What is the actual question.

Guess that was a waste of time.

--
Regards,
Tom Ogilvy


"Chad" wrote in message
...
Thanks, Tom.

I am getting the range reference from a userform and the worksheet
name is
included with the reference. I am currently using string functions to
extract the relevant reference. I was hoping there was a more elegant
solution, but it seems that there is not.

Chad




"Tom Ogilvy" wrote:

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