CODEWITHSUNDEEP
vbaexcelresizerange
Tom Kennedy
Tom Kennedy

Reputation: 58

Excel VBA Range Resize limit?

I'm using VBA to calculate pairwise slopes, store them in an array, then using Chip Pearson's technique of transposing the array on a workheet to sort them. My code fails when the number of slopes exceeds 65K, which would make sense in Excel 2003, due to number of rows. I thought it would work in Excel 2010, but I seem to have the same issue. Does anyone know if there's limitations to the Resize property or Transpose method?

Thanks

Sub pairwise()
Dim endrow As Long, i As Long, j As Long, s As Long
Dim num As Double, denom As Double, sij As Double
Dim r As Range
Dim slopes()

endrow = Range("A1").End(xlDown).Row
n = endrow - 1
nrd = endrow * n / 2
ReDim slopes(nrd)
Debug.Print LBound(slopes); UBound(slopes)
For i = 1 To n
For j = (i + 1) To endrow
    num = Cells(i, 2).Value - Cells(j, 2).Value
    denom = Cells(i, 1).Value - Cells(j, 1).Value
    If denom <> 0 Then
        sij = num / denom
        slopes(s) = sij
        s = s + 1
    End If
Next j
Next i

Set r = Range("C1").Resize(UBound(slopes) - LBound(slopes) + 1, 1)
    r = Application.Transpose(slopes)

    ' sort the range
    r.Sort key1:=r, order1:=xlAscending, MatchCase:=False
End Sub

Upvotes: 0

Views: 2441

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33175

I found the same limitation on the INDEX function. http://dailydoseofexcel.com/archives/2013/10/11/worksheetfunction-index-limitations/

Here's how you can make the output array a two dimensional array and read in all the values at once rather than inside a loop.

Sub pairwise()

    Dim lEndRow As Long
    Dim vaValues As Variant
    Dim aSlopes() As Variant
    Dim lCnt As Long
    Dim rOutput As Range
    Dim i As Long, j As Long

    'A 2d array here can easily be written to a sheet
    lEndRow = Sheet3.Range("a1").End(xlDown).Row
    ReDim aSlopes(1 To lEndRow * (lEndRow - 1), 1 To 1)

    'Create a two-d array of all the values
    vaValues = Sheet3.Range("A1").Resize(lEndRow, 2).Value

    'Loop through the array rather than the cells
    For i = LBound(vaValues, 1) To UBound(vaValues, 1) - 1
        For j = 1 + 1 To UBound(vaValues, 1)
            If vaValues(i, 1) <> vaValues(j, 1) Then
                lCnt = lCnt + 1
                aSlopes(lCnt, 1) = (vaValues(i, 2) - vaValues(j, 2)) / (vaValues(i, 1) - vaValues(j, 1))
            End If
        Next j
    Next i

    'Output the array to a range, and sort
    Set rOutput = Sheet3.Range("C1").Resize(UBound(aSlopes, 1), UBound(aSlopes, 2))
    rOutput.Value = aSlopes
    rOutput.Sort rOutput.Cells(1), xlAscending, , , , , , , , False

End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

It a limitation of the Transpose method.

My suggestion would be to declare your array as 2D from the start

Redim Slopes(1 To nrd, 1 To 1)

Also, your should use the Variant Array approach instead of looping over cells in your For loop

Upvotes: 1

Related Questions