Excel VBA load range into array overflow error

Question

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?

This link or this link doesn't answer your specific question but it offers better ways of finding the last cell in a column. — Automate This
– Automate This, Commented Oct 1, 2014 at 17:42
opt out of looping and load the range straight into an array in one line of code — user2140173
– user2140173, Commented Oct 1, 2014 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
– barryleajo, Commented Oct 1, 2014 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
– TxT, Commented Oct 1, 2014 at 22:05
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
– TxT, Commented Oct 2, 2014 at 0:48

Martin Tournoij · Accepted Answer · 2016-08-12 23:15:59Z

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.

edited Aug 12, 2016 at 23:15

Martin Tournoij

28k24 gold badges110 silver badges158 bronze badges

answered Aug 12, 2016 at 22:44

Margo T

961 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DavidSB · Accepted Answer · 2021-10-21 16:53:17Z

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.

answered Oct 21, 2021 at 16:53

DavidSB

111 bronze badge

Collectives™ on Stack Overflow

Excel VBA load range into array overflow error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related