How to get Excel data from multiple rows into a single cell

How to get Excel data from multiple rows into a single cell automatically using VBA. The Excel user wants to achieve the following as shown in the image:

get Excel data from multiple rows into a single cell

How to get Excel data from multiple rows into a single cell with spaces

Sometimes you may like to transfer the data using commas as separating entities as shown in the image:

get Excel data from multiple rows into a single cell

How to get Excel data from multiple rows into a single cell with commas

The data in the A column is the original data. The data in Range(“B1”) is the final result. The data in column A is first converted into unique values using the ‘COUNTIF’ worksheet function in the VBA code. Next the unique values are captured into a variable called uniqueValues. Then the data captured in the variable are placed in Range (“B1”). Finally the unique values in Range (“B2:B”) are cleared and we display on the result in Range (“B1”). The values can be separated by a space or a comma according to the needs of the user.
Watch the video below:

Watch this video on YouTube.

Here’s the VBA code for getting the data from multiple cells into a single cell separated by a space or comma:

Sub getUniqueValues()
Dim i As Long, lastRow As Long
Dim uniqueValues As Variant

Range(“B1”).ClearContents

For i = 1 To 10
If Application.CountIf(Range(“B:B”), _
Cells(i, “A”).Value) = 0 Then
Range(“B” & Rows.Count).End(xlUp).Offset(1, 0).Value = _
Cells(i, “A”).Value
End If
Next

lastRow = Range(“B” & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

‘The following line uses a comma to separate the values
uniqueValues = uniqueValues & Cells(i, “B”).Value & “,”

‘This remarked line uses a space to separate the values
‘uniqueValues = uniqueValues & Cells(i, “B”).Value & ” ”

‘MsgBox uniqueValues
Next

Range(“B1”).Value = uniqueValues
Range(“B2:B” & lastRow).ClearContents
Range(“B1”).Columns.AutoFit
Range(“B1”).Value = Mid(Range(“B1”), 1, Len(Range(“B1”)) – 1)

End Sub

Further reading:

How to import comma separated values into Excel

Leave a Reply

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