Merging rows quickly

[Lavina]

Hello guys,

im setting up a tool and one of the steps is to merge some data together.

It's a large document and my initial idea was to cycle back from last row, and check 2 cells, if they match between the 2 rows, a 3rd cell merges both row values and deletes the old one.

row nr /	1st value/	2nd value/	merge value
3000	/random	/match/	1
3001	/random	/match/	2

<tbody>
</tbody>

result:3000 /random/ match/ 3

This is too slow and I'm not sure how to approach it, the data size will only increase through out time.

Going row by row seems to be inefficient, but I can't think of a better way

 

[Fluff]

What code do you have at the moment?

 

[Lavina]

What code do you have at the moment?

Hello Fluff, sorry for the late reply, code:

 For counter1 = lastRow To 1 Step -1
        cell1Value = Range("E" + CStr(counter1))
        cell3Value = Range("A" + CStr(counter1))
        lastCycleCounter = Range("A" + CStr(counter1)).Address
        If Right(lastCycleCounter, 2) <> "$1" Then
            Cell2Value = Range("E" + CStr(counter1 - 1))
            cell4Value = Range("A" + CStr(counter1 - 1))
            If cell1Value = Cell2Value And cell3Value = cell4Value And Range("C" + CStr(counter1 - 1)) = Empty Then
                var1 = Range("G" + CStr(counter1)).Value
                var2 = Range("G" + CStr(counter1 - 1)).Value
                sumOfUnits = var1 + var2
                Range("G" + CStr(counter1 - 1)).Value = sumOfUnits
                Rows(counter1 & ":" & counter1).Delete Shift:=xlDown
            End If
        End If
    Next counter1

 

[Lavina]

Ended up with this:

    ReDim Preserve snapshotArrayFull(2 To lastRow, 1 To lastColumn)
    For i = 2 To lastRow
        If Cells(i, 5).Value = Cells(i - 1, 5).Value And Cells(i, 1).Value = Cells(i - 1, 1).Value Then
        snapshotArrayFull(positionT - 1, 7) = snapshotArrayFull(positionT - 1, 7) + Cells(i, 7)
        i = i + 1
        Else:
        For j = 1 To lastColumn
            snapshotArrayFull(positionT, j) = Cells(i, j).Value
        Next j
        positionT = positionT + 1
        End If
    Next i

Seems to be around 10 times faster than the prior approach

 

[Fluff]

Glad you sorted it & thanks for the feedback