Help with Concatenating Cells

  • Thread starter Thread starter Al Mackay
  • Start date Start date
A

Al Mackay

Wondered if it is possible to do the following.

I have a formula that concatenates data from several different cells.
However, I've now wanted to increase the amount of cells and it keeps
coming up with an error.

Is it possible to almost apply this logic within concatenation? (if I
want to do A1:A40)?

If so, could you tell me how to do this as I've struggled so far.

TIA - Al ( (e-mail address removed) )
 
Hi
do you want to concatenate cells A1:A40 in one single cell?. If yes you
may either use something like
=A1 & A2 & ....& A40

or you may download the free add-in Morefunc.xll
(http://longre.free.fr/english/)

It includes the function MCONCAT
use it like
=MCONCAT(A1:A40)
 
Al

Sub ReallyBigRow()
''take a column of cells and put all into one big row separated by commas
''originally posted by John Wilson
Dim LastRow As Long
Dim cell As Range
Dim DataRng As Range
Dim strRow As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A1:A" & LastRow)
For Each cell In DataRng
strRow = strRow & cell.Value & ","
Next cell
Range("B1").Value = strRow
End Sub

OR this UDF.............

Function ConRange(CellBlock As Range) As String
Application.Volatile True
For Each cell In CellBlock
ConRange = ConRange & cell.Value
Next
End Function

OR this UDF..........

Function ConCatRange(CellBlock As Range) As String
'comma-separated
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ","
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Gord Dibben Excel MVP
 
Back
Top