Concatenate UDF help please

[N Ramsay]

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

 

[Guest]

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.

 

[N Ramsay]

Gary''s Student - gsnu200721

Perfect - thanks so much

þ

NR

 

[Guest]

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