Excel "Overflow" error for 500,000 rows

Question

I have an excel sheet with 500,000 rows. It has 3 columns in the below format

 Staff    Locations     Roles
   1      Location1     Role1
   1      Location2     Role1
   2      Location2     Role2
   3      Location3     Role3
   3      Location3     Role4

The output comes in the below format

 Staff      Locations                 Roles
   1        Location1, Location2      Role1
   2        Location2                 Role2
   3        Location3                 Role3
   3        Location3                 Role4

I have the below vbscript I am using that works if the number of rows is small.

Sub Sort_Duplicates()

Dim lngRow, lngRow2 As Long

With ActiveSheet
    Dim flag As Integer: flag = 0
    Dim i As Integer
    Dim columnToMatch As Integer: columnToMatch = 1
    Dim column2ToMatch As Integer: column2ToMatch = 3
    Dim columnToConcatenate As Integer: columnToConcatenate = 2

    lngRow = .Cells(538537, columnToMatch).End(xlUp).Row
    .Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes

    Do
        If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then
         'flag = 1
         i = 1
         lngRow2 = lngRow
         Do While Cells(lngRow2, columnToMatch) = .Cells(lngRow2 - i, columnToMatch)
          If .Cells(lngRow2, column2ToMatch) = .Cells(lngRow2 - i, column2ToMatch) Then
            .Cells(lngRow2, columnToConcatenate) = .Cells(lngRow2, columnToConcatenate) & ", " & .Cells(lngRow2 - i, columnToConcatenate)
            .Rows(lngRow2 - i).Delete
          End If
         i = i + 1
         Loop
         lngRow2 = lngRow2 - 1
        End If

       ' If flag = 1 Then
       '  lngRow = lngRow2
       '  flag = 0
       ' Else
         lngRow = lngRow - 1
        'End If
    Loop Until lngRow = 1
End With
End Sub

When I try it for the sheet with 500,000 rows, it starts sorting but it throws a popup error "Overflow" with no additional messages. I tried to do this with 90,000 and still the same. Any suggestion how I can fix this issue?

Thanks in advance

Answer 1

Start by changing the Dim of all Integer variables to Long.