Run-time error '6'

Closed
sean douglas - Mar 20, 2010 at 06:29 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 20, 2010 at 09:26 PM
Hello all, I have a lot of rows and I do not want any duplicates in the first colum ( A ). When I have duplicates I want the whole row gone. My code is working for doing over 1000 rows ( I have tested it on smaller data sets than I need) but when I run this code on around about 52,200 lines I get this error message:
Run-time error `6': overflow
And here is the line that vba indicates the error is in
Numrows = Range("A65536").End(xlUp).Row
I have messed with this row, and have been researching the problem, but so far no luck.
The code I am using is below:
Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer
'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Numrows = Range("A65536").End(xlUp).Row
Range("A1:B" & Numrows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") = Cells(Iloop - 1, "A") Then
Rows(Iloop).Delete
End If
Next Iloop
'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End SubPlease help, I am brand new to VBA and need to get this working... I am really close.
Thank you!!!
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 20, 2010 at 09:26 PM
you have given dimensions like these

Dim Iloop As integer
Dim Numrows As integer


integer is too small

change it into long like this. I am sure it will work.

Dim Iloop As Long
Dim Numrows As Long
0