Array with a single value only

[tiredofit]

The code below works if there is data in A1 and a neighbouring cell.

Dim MyArray() As Variant

MyArray() = Cells(1, 1).CurrentRegion.Value

However, if there is only a value in A1, it crashes.

How can I make it work when there is only one value?

Thanks

 

[Fluff]

How about

Dim MyArray As Variant
MyArray = Cells(1, 1).CurrentRegion.Value

 

[Norie]

If you definitely want MyArray to be, well, an array try this.

Dim MyArray() As Variant

    With Cells(1, 1).CurrentRegion
    
        If .Cells.Count = 1 Then
            ReDim MyArray(1 To 1, 1 To 1)
            MyArray(1, 1) = .Value
        Else
            MyArray() = .Value
        End If
        
    End With

 

[tiredofit]

Thanks for both suggestions.

I've always added the brackets to clarify that it's an array, I forgot you can omit it.

 

[Fluff]

Glad we could help & thanks for the feedback