Excel VBA combine rows into cell

This Excel VBA tutorial explains how to combine rows into Cell.

You may also want to read:

Separate line break data into different rows

Delimit Cell value into rows

Excel VBA combine rows into cell

In my previous post, I demonstrated how to delimit a Cell into separate rows,  one example is that you copy a email list and you want to put each email address into different rows.

From:

Excel VBA delimit Cell value into rows 03

To:

Excel VBA delimit Cell value into rows 04

In this  post I will do a reverse version, I will combine rows back into cell, so that if you have an email list stored in different rows, you can combine them into one cell value and then copy it to the email sender list.

Incidentally, email list is just an example for this Macro, Microsoft Outlook and Gmail do allow you to copy  Excel rows of email address directly into the sender list without converting into a single cell first.

VBA Code – combine rows into cell

Press ALT+F11 and paste the below code into a Module

Public Sub combineRow()
    dataCol = "A"  'define the column you want to combine row
    Set resultRng = Range("B1")   'define the Cell you want to put the result    
    seperator = "; "  'define the sepeartor used to seperate for each data
    
    For i = 1 To Range(dataCol & Rows.Count).End(xlUp).Row
        tempString = tempString & seperator & Range(dataCol & i).Value
    Next
    resultRng.Value = Right(tempString, Len(tempString) - Len(seperator))
End Sub

Example – combine rows into cell

Suppose we have an email list in column A.

 

Define the separator in VBA code as “; ”

Define the resultRng as B1

Run the Macro, and the result displays in Range B1, each value is separated by colon.

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *