Saving worksheet range without formula to array

[nikki333]

Hi Folks

Trying to save an entire worksheet to an array (in order to trim the values), however, excluding cells that contain any formula.

So, my try was like so:

For i = numSektionProdukte + 3 To numZeilenProdukte  
            For j = 3 To numSpaltenProdukte
                If Not .Cells(i, j).HasFormula Then
                   arrProdukte(i - (numSektionProdukte + 2), j - 2) = .Cells(i, j)       'The first i = 9 and the first j is 3, so the first arrProdukte(1,1) would be 1,1 (Option base 1)
                End If
            Next j
        Next i

However, as soon as it comes to saving any value into the array, i get an error 9, Index out the range...

Any ideas

[Bob Phillips]

Dim you dimension arrProdukte before the loop? If so, to what size? It worked for me when I did so, but an alternative approach is load the array and clear the formula values

    arrProdukte = .Range(.Cells(numSektionProdukte + 3, 3), .Cells(numZeilenProdukte, numSpaltenProdukte))
    For i = numSektionProdukte + 3 To numZeilenProdukte
 
        For j = 3 To numSpaltenProdukte
 
            If .Cells(i, j).HasFormula Then
 
                arrProdukte(i - (numSektionProdukte + 2), j - 2) = Empty
            End If
        Next j
    Next i
    End With

[nikki333]

Thank you that works