Merging two Cells in multiple columns using VB/Macro

Question

I would like to merge two cells as one and I have to do that for 2000+ rows (1000+) if merged. I am looking for a macro that will help with this. Below is an example of what I would like to do..

I have used the basic macro recorder and its a lot of cells that I have hard code, I have 2003 rows that I need to do the below too.

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A28:A29,C28:C29,E28:E29,F28:F29").Select
    Range("F28").Activate
    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("A1").Select
End Sub

Below is an example of the data that I want to merge... https://i.sstatic.net/US0MG.jpg

Number  Def Name1   Name2   Group1  Group2
12345   1   abcd             1       2
12345   2   abcd             1       2
123456  1   abcde            5       8
123456  2   abcde            5       8
123789  1   qwert            2       5
123789  2   qwert            2       5

After merging , I would like to see the below: https://i.sstatic.net/Pz0tb.jpg

Number  Def Name1   Name2   Group1  Group2
12345    1  abcd                 1       2
         2              
123456   1  abcde                5       8
         2              
123789   1  qwert                2       5
         2

Thanks for your help in this matter!

Regards, Samit

Mr.Monshaw · Accepted Answer · 2013-08-08 19:07:54Z

1

Sub mergerizer()

Application.DisplayAlerts = False

Dim r As Integer
Dim mRng As Range
Dim rngArray(1 To 4) As Range
r = Range("A65536").End(xlUp).Row

For myRow = r To 2 Step -1

    If Range("A" & myRow).Value = Range("A" & (myRow - 1)).Value Then

        For cRow = (myRow - 1) To 1 Step -1

            If Range("A" & myRow).Value <> Range("A" & cRow).Value Then

                Set rngArray(1) = Range("A" & myRow & ":A" & (cRow + 1))
                Set rngArray(2) = Range("C" & myRow & ":C" & (cRow + 1))
                Set rngArray(3) = Range("E" & myRow & ":E" & (cRow + 1))
                Set rngArray(4) = Range("F" & myRow & ":F" & (cRow + 1))

                For i = 1 To 4
                    Set mRng = rngArray(i)
                    mRng.Merge
                    With mRng
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlTop
                        .WrapText = False
                        .Orientation = 0
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                        .MergeCells = True
                    End With

                Next i

                myRow = cRow + 1
                Exit For
            End If
        Next cRow
    End If
Next myRow

Application.DisplayAlerts = True

End Sub

its not crazy elegant but i tested it and it works :) good luck

answered Aug 8, 2013 at 19:07

Mr.Monshaw

4502 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samit Lal Over a year ago

Thank you so much Mr. Monshaw! that did the trick and saved me a lot of time.

Mr.Monshaw Over a year ago

my pleasure, pass the help along :)

Collectives™ on Stack Overflow

Merging two Cells in multiple columns using VB/Macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related