Union method for Range Object

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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.