Fix 'Run-time error 6: Overflow' in VBA Excel When Variable Exceeds Data Type Limit

The Error and When It Appears

You're deep in a VBA macro — looping through 50,000 rows, tallying sales figures, or watching a counter tick past its 33rd thousand — when Excel throws this at you:

Run-time error '6': Overflow

The macro stops cold. The VBA Editor opens and paints one line yellow. That's where VBA tried to stuff a number into a box too small to hold it.

Every VBA variable has a fixed numeric range. Push a value outside that range and you get Overflow. Nine times out of ten, the problem is an Integer variable doing a job that needs Long.

Root Cause

Each VBA data type holds only values within a specific range:

• Integer: –32,768 to 32,767
• Long: –2,147,483,648 to 2,147,483,647
• Single: ±3.4 × 1038 (limited precision)
• Double: ±1.8 × 10308
• Byte: 0 to 255
• Currency: –922,337,203,685,477.5808 to 922,337,203,685,477.5807

That 32,767 ceiling on Integer is the number that bites people. Excel has supported up to 1,048,576 rows since Excel 2007. Declare an Integer as your row counter and VBA crashes the moment you hit row 32,768. The sheet has a million possible rows. Your counter tops out at thirty-two thousand.

Step 1 — Find the Exact Line

Don't click End when the dialog appears. Click Debug instead. The VBA Editor opens and highlights the failing line in yellow — that's precisely where the overflow occurred.

Three patterns account for most cases:

' Counter declared as Integer, sheet has more than 32,767 rows
Dim i As Integer
For i = 1 To 100000  ' Crashes at 32,768
    ' ...
Next i

' Arithmetic result exceeds Integer range
Dim result As Integer
result = 200 * 200  ' 40,000 > 32,767 → Overflow

' Last used row returned as 50,000+ but stored in Integer
Dim rowCount As Integer
rowCount = Cells(Rows.Count, 1).End(xlUp).Row

Step 2 — Identify the Overflowing Variable

Hover over any variable on the highlighted line — the tooltip shows its current value. Still unclear? Drop a Debug.Print on the line before the crash:

Debug.Print "Value before overflow: " & yourVariable

Open the Immediate Window with Ctrl+G, then run the macro again. The printed value tells you exactly what number triggered the error.

Step 3 — Fix the Data Type

Replace Integer with Long

This single change resolves roughly 80% of Overflow errors. Any variable that counts rows, stores IDs, or accumulates totals that could exceed 32,767 should be Long:

' Before — blows up past row 32,767
Dim i As Integer
For i = 1 To lastRow
    ' process row
Next i

' After — handles all 1,048,576 rows without complaint
Dim i As Long
For i = 1 To lastRow
    ' process row
Next i

Fix Arithmetic That Overflows

There's a subtle trap here. VBA evaluates arithmetic using the types of the operands — not the type of the destination variable. Declaring result As Long doesn't help if you're multiplying two Integer literals. The expression overflows before the assignment ever happens.

' Trap: expression overflows before assignment
Dim result As Long
result = 200 * 200  ' 200 and 200 are Integers → Integer * Integer → Overflow

' Fix 1: Long literal suffix
result = 200& * 200

' Fix 2: Explicit conversion
result = CLng(200) * 200

' Fix 3: Declare operands as Long
Dim a As Long, b As Long
a = 200 : b = 200
result = a * b

Fix Single/Double Overflow

Astronomical figures or scientific calculations can exceed Long's ~2.1 billion ceiling. Switch to Double:

Dim bigNum As Double
bigNum = 3000000000# * 1500  ' # suffix forces Double literal

Fix Byte Overflow

Byte holds 0–255. Return a –1 error code into a Byte variable and you get Overflow immediately:

' Before
Dim status As Byte
status = someFunction()  ' Returns -1 on error → Overflow

' After
Dim status As Integer

Complete Fixed Example

A realistic scenario: summing a column across all used rows. The broken version works fine on a small test sheet — then silently explodes in production when the data grows past 32,767 rows.

' BROKEN — overflows on sheets with more than 32,767 rows
Sub ProcessData_Broken()
    Dim i As Integer
    Dim total As Integer
    Dim lastRow As Integer
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        total = total + Cells(i, 2).Value
    Next i
    
    MsgBox "Total: " & total
End Sub

' FIXED
Sub ProcessData_Fixed()
    Dim i As Long
    Dim total As Double    ' Double for sums that can grow large
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        total = total + CDbl(Cells(i, 2).Value)
    Next i
    
    MsgBox "Total: " & total
End Sub

Verify the Fix

• Press F5 to run. No error dialog means the overflow is gone.
• Print your row count to confirm large ranges are handled: Debug.Print "Processing " & lastRow & " rows".
• Test against a sheet with more than 32,767 rows if your data ever gets that large.
• Step through code with F8 and watch the Locals Window (View → Locals Window) to catch any variable creeping toward its type limit.

Defensive Habits Going Forward

• Default to Long, not Integer: Long is 4 bytes vs Integer's 2 bytes, but modern processors handle 32-bit integers natively — no practical performance difference. You drop Integer's range limitations and gain nothing bad.
• Use Option Explicit: Put Option Explicit at the top of every module. It forces variable declarations, which surfaces type decisions before they become runtime errors.
• Watch numeric literals: 200 is an Integer by default in VBA. Use type suffixes to be explicit: 200& for Long, 200# for Double.
• Validate external data: If a cell value drives an assignment, run IsNumeric() first and sanity-check the range before storing it in a typed variable.