Run-time error '6': Overflow

[msawyer]

Hello,

I have previously used the below VBA script to delete empty cells and shift all cells with values to the left in an excel document. When I tested the script on a small data set, it worked perfectly. My data set now includes 85 columns and almost 50,000+ rows. I'm getting a "Run-time error '6': Overflow" message. Since the script is using the .CurrentRegion property, I checked and made sure I don't have any empty rows or columns in my data set. Any thoughts on how to fix this?

Sub ShiftValuesLeft()
Dim iRows As Integer, iCols As Integer, x As Integer, y As Integer, icount As Integer
Dim MyArray(), rTop As Range
iRows = Range("Top").CurrentRegion.Rows.Count
iCols = Range("Top").CurrentRegion.Columns.Count - 1
Set rTop = Range("Top")
ReDim MyArray(iRows, iCols)


For x = 1 To iRows
For y = 1 To iCols
If rTop.Cells(x, y) <> "" Then
MyArray(x, icount) = rTop.Cells(x, y)
icount = icount + 1
End If

Next y
icount = 0

Next x

For x = 1 To iRows
For y = 1 To iCols
If MyArray(x, icount) <> "" Then
Range("Output").Cells(x, y) = MyArray(x, icount)
icount = icount + 1
End If
Next y
icount = 0
Next x

End Sub

[AB33]

May be these lines

Please Login or Register  to view this content.

Change currentregion to usedrange

[shg]

The Integer data type has a maximum value of 32767. In all your declarations, change Integer to Long.

[AB33]

shg,
Good spot!

[msawyer]

Excellent. Changing 'Integer' to 'Long' worked. Still took a few minutes to run but no error message. Thanks for the help. Much appreciated!