I want to be able to combine the contents of a range of cells into one cell.

Using the concatenate command includes spacing and/or characters for blank
cells.
ex: , , , , John Smith, , Joe Frazier, , etc.

Using the substitute trim commands puts excess characters inside cell values
ex: John, Smith, Joe, Franzier etc.

I want to be able to take the contents of non-blank cells in a row (15
columns) and combine them with a space and comma between the non blank cell
values.

Please help!

try this function:

in cell put

=merge(A1:H1)



Function merge(inrng As Range) As String
For Each cell In inrng
If Application.And(Len(cell) 0, cell < " ") Then
merge = merge & cell.Value & ", "
End If
Next
merge = Left(merge, Len(merge) - 2)
End Function

"crazymfr" wrote:

I want to be able to combine the contents of a range of cells into one cell.

Using the concatenate command includes spacing and/or characters for blank
cells.
ex: , , , , John Smith, , Joe Frazier, , etc.

Using the substitute trim commands puts excess characters inside cell values
ex: John, Smith, Joe, Franzier etc.

I want to be able to take the contents of non-blank cells in a row (15
columns) and combine them with a space and comma between the non blank cell
values.

Please help!

how do I get VBA to interact with Excel?

I dont have a lot of experience in VBA.

"Toppers" wrote:

try this function:

in cell put

=merge(A1:H1)



Function merge(inrng As Range) As String
For Each cell In inrng
If Application.And(Len(cell) 0, cell < " ") Then
merge = merge & cell.Value & ", "
End If
Next
merge = Left(merge, Len(merge) - 2)
End Function

"crazymfr" wrote:

I want to be able to combine the contents of a range of cells into one cell.

Using the concatenate command includes spacing and/or characters for blank
cells.
ex: , , , , John Smith, , Joe Frazier, , etc.

Using the substitute trim commands puts excess characters inside cell values
ex: John, Smith, Joe, Franzier etc.

I want to be able to take the contents of non-blank cells in a row (15
columns) and combine them with a space and comma between the non blank cell
values.

Please help!

Alt+F11

then Alt+I

Select Module from Menu

You will get a blank area (labelled "General")

Copy/paste code into module

HTH

"crazymfr" wrote:

how do I get VBA to interact with Excel?

I dont have a lot of experience in VBA.

"Toppers" wrote:

try this function:

in cell put

=merge(A1:H1)



Function merge(inrng As Range) As String
For Each cell In inrng
If Application.And(Len(cell) 0, cell < " ") Then
merge = merge & cell.Value & ", "
End If
Next
merge = Left(merge, Len(merge) - 2)
End Function

"crazymfr" wrote:

I want to be able to combine the contents of a range of cells into one cell.

Using the concatenate command includes spacing and/or characters for blank
cells.
ex: , , , , John Smith, , Joe Frazier, , etc.

Using the substitute trim commands puts excess characters inside cell values
ex: John, Smith, Joe, Franzier etc.

I want to be able to take the contents of non-blank cells in a row (15
columns) and combine them with a space and comma between the non blank cell
values.

Please help!