How to merge cells with pre-defined number

[ThrottleWorks]

Hi,
I will upload a sample file shortly and will notify the same.
I have data in three columns, A,B and C
I want to merge cell from Column B based on the values in Column A.
Column A wil have multiple values, multiple time (sorted)
For example
A1 = ABC
A2 = ABC
B1 = SAMPLE
B2 = SAMPLE
Then I need to merge B1 and B2.
Please note, value in B1 does not matter.
A3 = XYZ
A4 = XYZ
A5 = XYZ
B3 = SAMPLE
B4 = SAMPLE
B5 = SAMPLE
Then merge B3:B5.
Can anyone please help me in this.

 

[ThrottleWorks]

Hi,

Please find attached file for reference.

 

[Marc L]

Hi !

Try this !​

Sub Demo()
    With Application:  .DisplayAlerts = False:  .ScreenUpdating = False:  End With
With Sheet1.Cells(1).CurrentRegion
        N& = .Rows.Count:  F& = 2:  L% = 1
        K$ = .Cells(2, 1).Value & "¤" & .Cells(2, 2).Value
    For R& = 3 To N
        C$ = .Cells(R, 1).Value & "¤" & .Cells(R, 2).Value
        If C = K Then L = L + 1
        If C <> K Or R = N Then
            If L > 1 Then
                With .Cells(F, 2).Resize(L)
                     .HorizontalAlignment = xlCenter
                       .VerticalAlignment = xlCenter
                       .Merge
                End With
            End If
            K = C:  F = R:  L = 1
        End If
    Next
End With
    With Application:  .DisplayAlerts = True:   .ScreenUpdating = True:   End With
End Sub

 

[ThrottleWorks]

Hi @Marc L , thanks a lot for the help. I am trying this and will revert in case of any issue. Have a nice day ahead.

 

[ThrottleWorks]

Hi @Marc L , Sir where do you get '¤' this character. At first glance I thought this to be some sort of coding command.

Then I realise you have used it for concatenate. Nothing urgent, just curious.

Why do you have used 'R& = 3' line, I was not able to understand the reason. Tried but could not.

Also you use

With Application: .DisplayAlerts = False: .ScreenUpdating = False: End With

in a unique way. Saw for the first time.

PS - Sir, you have three If in the code, however there are only two End If in the code. Please correct me if I am wrong.

I was under impression that we must use equal no of If and End If.
Sorry if I am missing something.

Good night.

 

[ThrottleWorks]

Hi @Marc L Sir, sorry to trouble you. Please reply if you get time. This is not causing me any problem. Code is running fine.

I can see how 'K$' or ' C$' is defined. But I am not able to make out how C, K or L are defined. Where do C or K gets it value from.

Please correct me if I am missing anything.

 

[Marc L]

• Character ¤ comes from my local keyboard and is used to concatenate
• and avoid confusion between Mr yam aha and Mrs yama ha ! Aha ‼

• R variable is the row index, starting from row #3
• 'cause K variable yet contains row #2 concatenation …

• With Application: .DisplayAlerts = False: .ScreenUpdating = False: End With
• is the same as
• Application.DisplayAlerts = False : Application.ScreenUpdating = False …
• Read With VBA help and its sample.

• End If is not necessary with a single line …

• If C = K Then L = L + 1 is like this multi-lines block :​

    If C = K Then
       L = L + 1
    End If

Maybe both of these coding styles was used in my codes in your past threads …

• C & K variables are the rows concatenations …

Tip : progress in code in step by step mode via F8 key
and watch out variables values in Locals window …​

 

[Marc L]

Variables :

• N is the Number of rows of the source range.
• F is the First row index of the cells to merge.
• L is the Lines number of the cells to merge.
• K is the Key concatenation of the first row to merge.
• R is the Rows index.
• C is the Concatenation of row #R …
​

 

[ThrottleWorks]

Hi @Marc L , thanks a lot for the help. Sorry for late reply, was bit busy yesterday. I am going through the code once again.

Will disturb you in case I need more guidance. Have a nice day ahead.