Solved: Redim a range

[mikke3141]

Hello,

I'm trying to redimension a range that is used in an array.



With OldBook.Sheets(1)
ReDim UsedArea(.Cells.SpecialCells(11).Row, 17)
UsedArea = .Range("a21", .Range("Q" & .Cells.SpecialCells(11).Row)).Value
End With


When I use the code below



x = 10
ReDim Preserve UsedArea(1 To UBound(UsedArea, 1) + x, 1 To 2)


I get an error "Subscript out of range". Why and how can I fix this?
Thank you for any help.

[GTO]

Hi there,

From vba help:

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

In short...
ReDim Preserve UsedArea(1 To UBound(UsedArea, 1) + x, 1 To 2)
trying to increase the size of your first dimension while using Preserve will jam.

Hope that helps,

Mark

[mdmackillop]

You can often set your range to handle data in a "horizontal" configuration. You can then resize the desired value and then use Transpose to convert to the size for adding to listbox or whatever.

[mikke3141]

Thank you mdmackillop, now it works :rotlaugh:


Sub test()
Dim UsedArea
With ActiveWorkbook.Sheets(1)
ReDim UsedArea(.Cells.SpecialCells(11).Row, 4)
UsedArea = .Range("a1", .Range("D" & .Cells.SpecialCells(11).Row)).Value
End With
UsedArea = Application.Transpose(UsedArea)
ReDim Preserve UsedArea(1 To UBound(UsedArea, 1), 1 To UBound(UsedArea, 2) + 1)
UsedArea = Application.Transpose(UsedArea)
MsgBox UBound(UsedArea, 1)
End Sub

[mdmackillop]

Not quite what I meant, but as long as it works!