Concatenate contiguous fields on one worksheet into 1 field on ano

[MargeB]

I have an Excel workbook that has 2 worksheets. One worksheet (called Master)
has company and address information in separate, contiguous cells. I need to
concatenate those cells into a single cell on the other worksheet, with line
breaks between.

Example:
Company Name
Address Line 1
Address Line 2
City, ST Zipcode

Some addresses will not have the Address Line 2, and I don't want to show a
blank line.

Thanks

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range) As String
'ignores blank cells
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & vbLf
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(Master!A1:A4)


Gord Dibben MS Excel MVP

 

[MargeB]

Thanks for the reply. I haven't had a chance to try it yet.