Concatenate formula

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have a list of items down column B. The number of items in column B will vary from time to time.

I am trying to concatenate each row in B (concatenate(B17","B18,".... I need them concatenated No spaces and separated by a ","

My problem is how do I only concatenate down the list to where there is no more data. I don't want to write this extensive formula that will end up with a bunch of ,,,,,,,, at the end.

I assume there is a way using VBA but I don't know how.

My sheet name is "Input" My data starts in B17 and goes down to a variable row. I want to put the concatenated value in B2

Any help us very much appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
What version of Xl do you have?
 
Upvote 0
Try this. I haven't tested it but it should get you in the ball park

Code:
Option Explicit

Sub Concat()

Dim wsSheet1 as excel.worksheet
Set wsSheet1 = Sheets("Sheet1")
Dim rngTable1 as Range
Dim rngB as Range
Set rngTable1 = wsSheet1.Range("B17").CurrentRegion 'insert a line above row 17 so it doesn't grab data above it

Dim rngRows as Long, rngRows1 as Long
Set rngRows as rngTable1.Rows.Count
Set rngRows1 as rngTable1.Rows(1).Count
Dim i as long
Dim conc as string
Dim tconc as string

For i = rngRows1 to rngRows
conc = Range(i & "B").Value & ","
tconc = tconc & conc
Next

End Sub
 
Upvote 0
Try this. I haven't tested it but it should get you in the ball park

Code:
Option Explicit

Sub Concat()

Dim wsSheet1 as excel.worksheet
Set wsSheet1 = Sheets("Sheet1")
Dim rngTable1 as Range
Dim rngB as Range
Set rngTable1 = wsSheet1.Range("B17").CurrentRegion 'insert a line above row 17 so it doesn't grab data above it

Dim rngRows as Long, rngRows1 as Long
Set rngRows as rngTable1.Rows.Count
Set rngRows1 as rngTable1.Rows(1).Count
Dim i as long
Dim conc as string
Dim tconc as string

For i = rngRows1 to rngRows
conc = Range(i & "B").Value & ","
tconc = tconc & conc
Next

Range("B2").Value = tconc

End Sub

Sorry added the value to B2 here
 
Upvote 0
If you have 365 or 2019 version you can use this formula
=TEXTJOIN(",",1,B17:B1000)

otherwise
Code:
Sub gheyman()
   Dim Cl As Range
   Dim St As String
   For Each Cl In Range("B17", Range("B" & Rows.Count).End(xlUp))
      If Not Cl = "" Then St = St & Cl & ","
   Next Cl
   Range("B2").Value = Left(St, Len(St) - 1)
End Sub
 
Upvote 0
2016 so TextJoin, didn't work. But code did work!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
But code did work!
If your data between cell B17 and the last data cell in Column B will never have any blank cells between those two cell limits (in other words, if all blank cells always occur after the last data item), then you can use this somewhat simpler code as well...
Code:
Sub gheyman2()
  Range("B2") = Join(Application.Transpose(Range("B17", Range("B17").End(xlDown))), ",")
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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