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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >