Run-time Error '6' :Overflow

[Nieriel]

Hi All,
I am wondering if anyone can help me, I am having some issues with one of my macros. I keep getting a Runtime Error '6' when I try and run my macro on a larger set of data - the exact same macro is fine on smaller data and works fine however the workbook I need to run this marco is now in excess of 56k rows and it's started throwing up this error - the culprit seems to be the "For i = 2 to lr", I have i as Integer and lr as long - my exact macro is below (the text in red is what the debugger seems to identify as the problem). Anyone have any ideas on how I can make this work for a very large set of data? I am using 64bit version of excel and it is saved as xslx macro enabled workbook.

Any help appreciated

KR

Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 12
Set ws = Sheets("Export")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:N1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move After:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

 

[Eric W]

Try changing:

Dim vcol, i As Integer

to

Dim vcol, i As Long

The integer Data Type maxes out at 32,767, so if you have more rows than that, you need to go to a Long variable.

 

[Jonmo1]

Dim vcol, i As Long

Also, dispite what logic and previous programming experience may tell us, that line Dims vcol as Variant.
When declaring multiple variables with specific DataTypes in the same Dim statement, you must specify the DataType for each variable..

Dim vcol As Long, i As Long

 

[Nieriel]

Thanks both, I tried both of these and both of them worked - thanks again for your help!