1

Below code loads a range into an array:

Dim Data() As Variant
Dim i As Long

Erase Data

i = 2: Do Until Sheets("whatever").Cells(i, 1) = "": i = i + 1: Loop
Sheets("whatever").Activate
Data = Sheets("whatever").Range(Cells(2, 1), Cells(i, 37)).Value

This used to work but since today (bits of new data?) gives me Run-time error '6' / Overflow in the line that loads the Data array.

i = 242703 which is indeed the last row I want to load. Can this be two big for an array?

TxT
  • 51
  • 4
  • 1
    [This link](http://www.cpearson.com/excel/LastCell.aspx) or [this link](http://www.ozgrid.com/VBA/ExcelRanges.htm) doesn't answer your specific question but it offers better ways of finding the last cell in a column. – Automate This Oct 01 '14 at 17:42
  • [opt out of looping and load the range straight into an array in one line of code](http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730) –  Oct 01 '14 at 17:59
  • Your code runs fine for me on 278575 rows and 37 columns each containing an integer value. Perhaps need to look elsewhere in your code/data for the problem? – barryleajo Oct 01 '14 at 21:44
  • @barryleajo. Tks but where elsewhere? Can it be the my worksheet is corrupted or something? How can I check? Indeed this code run fine in many other files I have. – TxT Oct 01 '14 at 22:05
  • Integer variables where there 'capacity' is being exceeded; divide by zero errors would be my starting point. You could try loading your array in a loop, trapping any error and examine your data/variables at that point. Otherwise Google is a wonderful thing! – barryleajo Oct 01 '14 at 22:58
  • 1
    It turn out the issue was that some cells for whatever reason where formatted as date while the value in the cell was negative. That generated an 'error' (not sure how this is called) in the cell so that the it would not fit into a variant. – TxT Oct 02 '14 at 00:48

2 Answers2

1

Here's how I fixed this:

Create the new file, create module in it and copy all the text of macro into module of new file. The Runtime Error 6 (Overflow) error disappeared after I did this.

Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
Margo Grig
  • 11
  • 2
1

Error 6 (overflow) will be triggered if there is an error on the sheet being referenced.

I received this on a sheet where the first column was of a date type but contained numbers were too large for a legal date. On the sheet these numbers showed up as "############.....#" (number of # depending on width of column). The excel Error Checking function did not catch this type of error.

DavidSB
  • 11
  • 1