Yesterday i got this error when trying to put all the info from the hundreds cells that are populated into one cell.
- My arguments exceed argument limit of concatenate formula. In 2007 concatenate supports only 255 arguments.
There are some resolution to get this done.
- Concatenate “Inception“.
For example you’ll concat all Cells from A1 to C100. So you should create 4 concatenations:
- Concat A1 to A100, locate it on cell A101
- Concat B1 to B100, locate it on B101
- Concat C1 to C100, locate it on C101
- Combine A101, B101 and C101 into one cell.
Here’s my code to accomplish this purpose
Function ConCatRange(CellBlock As Range) As String 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
And just write =concatrange(A1:C100) hope it’ll work like a charm.