ReDim Preserve in For Loop

Question

I don't know what I am doing wrong as the code below is able to ReDim Preserve the first iteration but not the second.

Dim inj0() As Variant
Dim i As Integer
Dim c As Integer
Dim Rng As Range
Dim pos As Integer

'Find the last used column in a Row
Dim LastCol As Integer
With ActiveSheet
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With

c = 0
For i = 1 To LastCol
    pos = InStr(Cells(2, i), "80")
    If pos = 1 Then
        ReDim Preserve inj0(c, 2)
        inj0(0, 1) = "80"
        Set Rng = Cells(2, i)
        inj0(c, 2) = Rng.Offset(-1, 0).Value
        inj0(c, 0) = Rng.Offset(3, 0).Value
        c = c + 1
    End If
Next

At `ReDim Preserve inj0(c, 2)` it says subscript out of range — peetman, Oct 03 '16 at 09:02
You can only `ReDim preserve` the _last_ dimension of an array. From online 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_ — chris neilsen, Oct 03 '16 at 09:04
Possible duplicate of [ReDim Preserve "Subscript Out of Range"](http://stackoverflow.com/questions/23393123/redim-preserve-subscript-out-of-range) — Comintern, Oct 03 '16 at 14:35
One optimization I'd consider here. Avoid using Redim Preserve, it is a costly operation. Instead, determine how many entries are going to be needed first then define the size, then fill the array. Something like doing a CountIf first to determine how many entries are needed would be better, especially if there is going to be a lot of data added to the array. — Ryan Wildry, Oct 03 '16 at 14:47

score 0 · Accepted Answer · answered Oct 03 '16 at 09:16

Try to change the code as follows:

c = 0
ReDim inj0(2, 0)
inj0(1, 0) = "80"
For i = 1 To LastCol
    pos = InStr(Cells(2, i), "80")
    If pos = 1 Then
        ReDim Preserve inj0(2, c)
        Set Rng = Cells(2, i)
        inj0(2, c) = Rng.Offset(-1, 0).Value
        inj0(0, c) = Rng.Offset(3, 0).Value
        c = c + 1
    End If
Next

If you need the dimensions to be swapped, finally you can apply WorksheetFunction.Transpose method.

ReDim Preserve in For Loop

1 Answers1

Linked