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