*HELP* Merge rows if 2 cells partially match

[Legacy 171839]

Hi,

Proper stuck here.

Problem - Need some vba to do the following:

Criteria: Identify if 1st 12 characters in A1 match 1st 12 characters in A2.
Action: If they do, merge rows 1 and 2 (data wont overlap; b1 may have data, c2 may have data. We want to keep both in merged row. B1 and B2 will never both contain data).
Code should look down the whole of Column A and merge all rows where the cell 1st 12 characters of the cell match the 1st 12 of cell directly below it.
Hope this makes sense, and really hope you can help.

Thanks in advance

 

[Andrew Poulsom]

Can there only ever be 2 rows that you want to merge? Wouldn't it be better to have the data on only 1 row, deleting the second?

 

[Legacy 171839]

Can there only ever be 2 rows that you want to merge? Wouldn't it be better to have the data on only 1 row, deleting the second?

To explain - may look like this

Excel12345AB 40 -- 60 90
Excel12345AC -- 50 -- --

We would want the above to look like

Excel 12345 40 60 50 90

Both rows merged to one. Hope thats clearer...

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim r As Long
    Dim c As Long
    With ActiveSheet.Range("a1").CurrentRegion
        For r = .Rows.Count To 2 Step -1
            If Left(.Cells(r, 1).Value, 12) = Left(.Cells(r - 1, 1).Value, 12) Then
                For c = 2 To .Columns.Count
                    If .Cells(r, c).Value <> "" Then
                        .Cells(r - 1, c).Value = .Cells(r, c).Value
                    End If
                Next c
                .Rows(r).Delete Shift:=xlUp
            End If
        Next r
    End With
End Sub

 

[Legacy 171839]

Thank you for quick reply - will give it a go and let you know.