ReDim multidimensional array problem

I am having some problems ReDiming a multidimensional array. Originally I do a ReDim to dimension it then start this loop. My goal is that if I find a value I want to take it out of the multidimensional array so it will not longer be compared to anything. My error is the normal 'Subscript out of range' and points to:
ReDim Preserve gaMatrix(iNewRows, iEndFieldPos)

Does anyone know if this is even possible to do? Or do I have to Erase it all then do the ReDim?

Thanks for any help.


Here is my code below. Please respond with any questions.

For iACnt = 0 To UBound(gaMatrix)
If gaMatrix(iACnt, 0) = UCase$(Format$(xlBook.Worksheets(sWorksheet).Cells(iExcelStartRowPos, 1).Value, "mmmyy")) Then
For iCnt = iStartFieldPos To iEndFieldPos
xlBook.Worksheets(sWorksheet).Cells(iExcelStartRowPos, iExcelStartFieldPos).Value = gaMatrix(iACnt, iCnt)
iExcelStartFieldPos = iExcelStartFieldPos + 1
DoEvents
Next iCnt

'START TEST SECTION ##**##**##**##**##
If iACnt < UBound(gaMatrix, 1) Then
For iMoveCnt = iACnt + 1 To UBound(gaMatrix, 1)
For iFieldPos = 0 To UBound(gaMatrix, 2)
gaMatrix(iMoveCnt - 1, iFieldPos) = gaMatrix(iMoveCnt, iFieldPos)
Next
Next
MsgBox UBound(gaMatrix, 1) & " " & UBound(gaMatrix, 2)
iNewRows = UBound(gaMatrix, 1) - 1
ReDim Preserve gaMatrix(iNewRows, iEndFieldPos)
Else
If iACnt = iArrayCnt Then
ReDim Preserve gaMatrix(iNewRows, iEndFieldPos)
Else
'Error
End If
End If
'END TEST SECTION ##**##**##**##**##

Exit For
End If
Next iACnt

As i learnt not a long time ago (yesterday :) ), you can't use preserve when changing anything other than the last 'dimention'.


So you cannot do

redim a(6,8)
redim preserve a(8,24)

I suggest using a second (tempory) array, then making it the same size as the first (not using preserve), then copy everything into that, then redim the original and copy the contents back.