Merge cells in column A in function of the number of items written in column B

[Doflamingo]

Hi all,

I’m looking for the lines of code that would allow me to merge cells in column A where the number of cells merged in that column represent the number of items written in the column B.

Here is the code of the listbox 1 and listbox 2 that put items from those listboxes in the column B of an excel sheet

For b = 0 To ListBox1.ListCount - 1
With Sheets("Data")
     nextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
     .Range("B" & nextRow) = ListBox1.List(b)
End With
Next
For c = 0 To ListBox2.ListCount - 1
With Sheets("Data")
     nextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
     .Range("B" & nextRow) = ListBox2.List(c)
End With
Next

Here is the code of a previous thread that merge cells in column B where the number of cells merged in that column represent the number of items which have the same syntax written in the column A

Sub MergeBbasedonValinA()
Dim cellsA As Range
Dim i As Long
i = 1
For Each cellsA In Sheets("Sheet1").Range("A2:A50") 'expand range to your needs
    If cellsA.Value <> cellsA.Offset(1, 0).Value Then
        Range("B" & i & ":B" & cellsA.Row).Merge
        i = cellsA.Row + 1
    End If
Next cellsA
End Sub

Here is the thread: Merge cells by count vba

Does anyone have an idea about how to adapt those lines of code to a listbox from a userform ?

Many thanks in advance

 

[DanteAmor]

Dear @DanteAmor

So many thanks for your help

Here is what I was looking for, I've changed in red the item that had to be changed to get what I wanted

With Sheets("Data")
        lr1 = .Range("B" & Rows.Count).End(xlUp)(4).Row
        .Range("B" & Rows.Count).End(xlUp)([COLOR=#ff0000]4[/COLOR]).Resize(ListBox1.ListCount).Value = ListBox1.List
        .Range("B" & Rows.Count).End(xlUp)(2).Resize(ListBox2.ListCount).Value = ListBox2.List
        lr2 = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("A" & lr1 & ":A" & lr2).Merge
        .Range("A" & lr1 & ":A" & lr2).Value = TextBox1.Value
       [COLOR=#0000ff] .Range("A" & lr1 & ":B" & lr2).Borders.LineStyle = xlContinuous[/COLOR]
End With
ListBox1.Clear
ListBox2.Clear
TextBox1 = ""

So now I'm looking for how to get border for each filled cell

Add that line

 

[Doflamingo]

Hello @DanteAmor

Thank you so much for your help and your time

That was exactly what I was looking for

Kind regards

Doflamingo

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.