Run-time error '6'

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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!!!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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