Concatenate formula

[gheyman]

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.

 

[Fluff]

What version of Xl do you have?

 

[SupremeDr]

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

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

 

[SupremeDr]

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

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

 

[Fluff]

If you have 365 or 2019 version you can use this formula
=TEXTJOIN(",",1,B17:B1000)

otherwise

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

 

[gheyman]

2016 so TextJoin, didn't work. But code did work!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

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...

Sub gheyman2()
  Range("B2") = Join(Application.Transpose(Range("B17", Range("B17").End(xlDown))), ",")
End Sub