Thanks Tushar
The problem with that is that when I then refer to myarray(i, 5) in a For
loop with i as the variable and there was originally no value for the
usedrange's first row's fifth column I get a subscript out of range error.
Therefore, I tried to define the array as 5 columns wide which I thought
would avoid this error

The code was just example code, here is code that compiles:

Sub test()
Dim myarray() As Variant
Dim myvar As Integer
myvar = Worksheets("Sheet1").UsedRange.Rows.Count
ReDim myarray(1 To myvar, 1 To 5)
myarray = Worksheets("Sheet1").UsedRange.Value
Debug.Print UBound(myarray, 2)
End Sub


Put any value in cells a1,b1,c1,d1 on Sheet1 and the immediate window will
show UBound(myarray, 2) as 4 even though you defined the array as 5 columns
wide...?? What's with this? How can I avoid an error when referring the 5
column of the array then? Preferably not with an On Error statement
Thanks
Matt


"Tushar Mehta" wrote in message
...
A couple of points.

The code you've shared doesn't compile. You might want to check what that
is all about.

As far as changing the dimension of your array goes, it appears XL/VBA
redimensions the array as needed. After all, it is possible that the
specified array size is insufficient to transfer the specified range.
Effectively, your code could be simplified to:

Dim myarray() As Variant
myarray = Worksheets("sheet1").UsedRange.Value
MsgBox UBound(myarray, 2) & "," & UBound(myarray, 1)


"Matt Jensen" wrote:

Howdy
I've defined a dynamic array

dim myarray() as variant
dim myvar as int
myvar =Worksheet("myname").UsedRange.Rows.Count
redim myarray(1 to myvar, 1 to 5)
myarray = Worksheet("myname").UsedRange.Value

Why then, when the usedrange is actually 4 columns wide (& 1 row deep)
and
the 5th column is empty, does
UBound(myarray, 2) = 4
???
Doesn't that defy the whole point of dimensioning the array?

What am I missing?
Thanks
matt