Runtime Error 6 in VBA: “Overflow,” explained and fixed

Error 6 — “Overflow” — means a calculation produced a number outside the range of the variable (or the intermediate type) meant to hold it. The classic case: an Integer variable asked to hold a row number above 32,767 in a modern spreadsheet. The math was fine; the container was too small.

What it means

VBA types have hard limits: Integer tops out at 32,767, Long at about 2.1 billion, Byte at 255, Single/Double at astronomically larger values. Overflow fires when a value crosses its container's limit — including mid-calculation: 20000 * 2 overflows if VBA does the multiplication in Integer space before the result reaches a Long variable.

The usual causes, most common first

Row/column counters declared as Integer. Worksheets have 1,048,576 rows; an Integer counter dies at row 32,767. The single most common overflow in Excel VBA.
Intermediate math in small types — the result variable is Long, but the expression's pieces are Integers, so the multiplication overflows before assignment.
Division by a value that makes the result explode, or converting a huge Double with CInt() — conversions overflow exactly like assignments.
Loop counters and accumulators that grew past the type as data volumes grew — code that worked for years on smaller files.
Timer/clock arithmetic — subtracting or adding large tick counts in small types.

The fix

Declare counters and sizes as Long. In modern VBA there is no performance reason to use Integer for anything countable — Dim i As Long is the house standard that deletes this error class.
Force wide intermediates: convert one operand so the whole expression computes wide — CLng(a) * b, or write constants with a type suffix (20000&).
Use the right conversion: CLng() instead of CInt() when values can exceed 32,767 — and guard genuinely huge inputs when even Long isn't enough (then it's Double territory, with its precision trade-offs).
On the failing line (click Debug), hover each variable: the one at its type's ceiling (32,767 or 2,147,483,647) is the container that burst.

Do not download anything for this error. It's one variable sized too small inside the macro. The repair is a declaration.

If it still happens

If every declaration looks wide, the overflow is in a conversion or an API/property that itself uses a smaller type — check what the failing call expects (some older properties are Integer-typed no matter what you pass). Print intermediate results with Debug.Print walking up to the line; overflow always announces which quantity crossed which limit.

Common questions

Why did old code start overflowing now? Data grew. Integers were safe when sheets had hundreds of rows and files were small; modern row counts and volumes crossed the 32,767 ceiling that was always there.

Integer or Long — does it matter for speed? On modern hardware, no meaningful difference. Long is the correct default for anything that counts rows, records, or bytes.