VBA that combine selected cells in one cell and number them

[Folsen]

I have a huge spreadsheet that I'm trying to consolidate some lines in.
I have information in Column A, B, C and D, so I need to be able to select cells in any Column, but 1 at the time.

What I want to do is select 2 (or more) cells in Column A, and when I run the VBA it will:
- Concatenate the text from selected cells, in the first cell I selected, AND
- I want the text from each cell to be in it's separate line (same as the Alt+Enter), AND
- I want to add "a)", "b)", etc. in front of the text of each cell.

Original Data can look like this (data in 4 separate cells):
[TABLE="width: 239"]
<tbody>[TR]
[TD]Audi - is German
[/TD]
[/TR]
[TR]
[TD]Bentley - is expensive
[/TD]
[/TR]
[TR]
[TD]Chevy - is a solid car
[/TD]
[/TR]
[TR]
[TD]Dodge - have a long history with Pickup Trucks
[/TD]
[/TR]
</tbody>[/TABLE]



Data after I run the VBA, look like this (Data is now in 1 cell w/alphabetic bullet/numbering);
[TABLE="width: 239"]
<tbody>[TR]
[TD]a) Audi - is German
b) Bentley - is expensive
c) Chevy - is a solid car
d) Dodge - have a long history with Pickup Trucks
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try:

Sub ConcatAndOrg()
Dim ctr As Long, str1 As String, c As Range

    ctr = 0
    str1 = ""
    For Each c In Selection
        ctr = ctr + 1
        str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf
    Next c
    Selection.ClearContents
    Selection.Cells(1, 1).Value = str1
    
End Sub

If you select cells A1:A3, A7 from this layout, column A will end up looking like column C here:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Audi - is German[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]a) Audi - is German
b) Bentley - is expensive
c) Chevy - is a solid car
d) Dodge - have a long history with Pickup Trucks
[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bentley - is expensive[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Chevy - is a solid car[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Edsel - long gone[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Edsel - long gone[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ford - another car company[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ford - another car company[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BMW - also expensive[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BMW - also expensive[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Dodge - have a long history with Pickup Trucks[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

</tbody>

Sheet3​

Also note that when you say you want it in the first cell you select, there's really no way to tell that. Excel helpfully organizes the selected range for you. So I just put the results in the upper left corner of the range.

I'd assume you'd want to open the macro selector and assign a hot-key to this macro to make it easier to run.

Let me know if this works for you.

 

[Folsen]

That is AWSOME works great! Thanks a lot for your help Eric

I don't understand VBA, but I am starting to learn because it's such a great tool.

Can I ask if you could help out with one more?
If I select 2 or more (same as before), consolidate to 1 cell, but use "Strikethrough" for all but the first?
Is that possible?

 

[Eric W]

Glad it works for you! The strikethrough part just takes a little tweak:

Sub ConcatAndStrike()
Dim ctr As Long, str1 As String, c As Range, beg As Long

    ctr = 0
    str1 = ""
    For Each c In Selection
        ctr = ctr + 1
        str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf
        If ctr = 1 Then beg = Len(str1) + 1
    Next c
    Selection.ClearContents
    Selection.Cells(1, 1).Value = str1
    Selection.Cells(1, 1).Characters(Start:=beg, Length:=Len(str1)).Font.Strikethrough = True
    
End Sub

 

[Folsen]

Works like a charm!!
Just one thing, I do not need the a), b), etc. here. Sorry for not specifying that.

 

[Eric W]

No problem! Just change this line:

str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf

to

str1 = str1 & c.Value & vbCrLf

 

[Folsen]

Works like a charm Awesome

 

[Eric W]

You're welcome!