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

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

Code:
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

Code:
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
 
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

Code:
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
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top