Hi

I need some help creating a UDF that concatenates a RANGE of cells as
follows:-

1. If the cell is BLANK, do not concatenate it
2. If the cell is NOT blank, add a BULLET and SPACE to the start of
the text, concatenate it and add a <CR to the end.

This is to create a bulleted list of non-blank cells in a range, with
a <CR between each entry.

so if the range (a single column of data) looks like:

<blank
hello world
<blank
<blank
more data
<blank
end
<blank

I would end up with a text string as follows:

—� hello world
—� more data
—� end

Can anyone please help?

Many Thanks in advance...

NR

Function splice_um(rr As Range) As String
Dim s As String
s = ""
bullet = ". "
cr = Chr(10)
For Each r In rr
If Not IsEmpty(r) Then
s = s & bullet & r.Value & cr
End If
Next
splice_um = s
End Function

Used a period for a bullet. Text wrapping must be on to see the column.
--
Gary''s Student - gsnu200721

On May 14, 5:47 pm, Gary''s Student <snip wrote some good code:

Gary''s Student - gsnu200721

Perfect - thanks so much

:oþ

NR

Try this. Assume data is in column A

Sub merge_cells()

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

ConcatenateWord = ""
RowCount = 1
For NextRow = 2 To Lastrow

If IsEmpty(Cells(NextRow, "A")) Then

If ConcatenateWord < "" Then

Cells(RowCount, "A") = "€¢ " + ConcatenateWord
RowCount = RowCount + 1
ConcatenateWord = ""
End If
Else
ConcatenateWord = ConcatenateWord + " " + Cells(NextRow, "A")
Cells(NextRow, "A") = ""
End If

Next NextRow

If ConcatenateWord < "" Then
Cells(RowCount, "A") = "€¢ " + ConcatenateWord
End If


End Sub



"N Ramsay" wrote:

Hi

I need some help creating a UDF that concatenates a RANGE of cells as
follows:-

1. If the cell is BLANK, do not concatenate it
2. If the cell is NOT blank, add a BULLET and SPACE to the start of
the text, concatenate it and add a <CR to the end.

This is to create a bulleted list of non-blank cells in a range, with
a <CR between each entry.

so if the range (a single column of data) looks like:

<blank
hello world
<blank
<blank
more data
<blank
end
<blank

I would end up with a text string as follows:

—� hello world
—� more data
—� end

Can anyone please help?

Many Thanks in advance...

NR