2

I'm getting an Overflow area in the following sub, and I can't figure out why. Stepping through the code, lRows and lCols gets set to the correct values, and the redims set the correct ranges on the arrays, but it fails when I try to assign the range values to the array (on line: arrData = rng.value). My rows do often go up to around 90,000+, but I have everything as long, so I would think that wouldn't be a problem...

Sub test()
Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

lRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
lCols = ActiveSheet.Range("A1").End(xlToRight).Column

ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)
Set rng = ActiveSheet.Range(Cells(1, 1), Cells(lRows, lCols))
arrData = rng.value ' Overflow error on this line
For j = 1 To lCols
    For i = 1 To lRows
        arrReturnData(i, j) = Trim(arrData(i, j))
    Next i
Next j

rng.value = arrReturnData
End Sub
Methonis
  • 49
  • 1
  • 7
  • What you're doing is not anything I've tried, but I've never seen anybody try to set an array that way. Do you get the same result if you copy cell by cell? – Ann L. Nov 06 '13 at 20:10
  • 1
    Just had a mini-stroke, and suddenly it dawned on me to try .Value2 instead of .value. Fixed the problem right up. – Methonis Nov 06 '13 at 20:17
  • You don't need to `ReDim arrData`. In fact you should declare `arrData As Variant`, not as an array. – Jean-François Corbett Nov 07 '13 at 08:45

1 Answers1

0

try 

Dim arrData as Variant
arrData = Range(Cells(1, 1), Cells(lRows, lCols))

and for more info see this answer

Community
  • 1
  • 1