I had applied it to a column, so use this to change it to a row (in this
case row 1 set the value at the top if you need another row).... I also
added a check that if the number of items is less then 2 then the addition
of an 'and' is excluded.

'
Sub Sentence()
Dim xlc As Integer, xc As Integer
Dim xSentence As String, xValues As Integer
Dim xrow as long

'set row to act upon
xrow = 1

With Sheets("Sheet1")
xValues = 0
xlc = .Cells(xrow, .Columns.Count).End(xlToLeft).Column

xSentence = "This store carries the following goods "

For xc = 1 To xlc
If Len(Trim(.Cells(xrow, xc))) 0 Then
'use this value
xValues = xValues + 1
If xc = xlc Then
If xValues 1 Then
xSentence = xSentence & " and " & Trim(.Cells(xrow, xc)) &
"...."
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) & "...."
End If
Else
xSentence = xSentence & Trim(.Cells(xrow, xc)) & ", "
End If
End If
Next
End With
MsgBox xSentence
End Sub


--
Cheers
Nigel



"Ariel" wrote in message
...
Hi Nigel

Thanks for your help. I have two questions:

The code you wrote puts an "and" in front of a single value, so that it
may
says:

This store carries the following goods and nails...

Also, can you please show me how the code would be written to evaluate
across a distinct number of cells in a row. For example:

A1: B1: C1: D1: E1:
paint nails null screws null

In this scenario, the code should evaluate the following array only A1:E1.

Thanks again for your help!

Ariel



"Nigel" wrote:

Try this ........ change the sheet name to suit.....

Sub Sentence()
Dim xlr As Long, xr As Long, xSentence As String
With Sheets("Sheet1")
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row

xSentence = "This store carries the following goods "

For xr = 1 To xlr
If Len(Trim(.Cells(xr, 1))) 0 Then
'use this value
If xr < xlr Then
xSentence = xSentence & Trim(.Cells(xr, 1)) & ", "
Else
xSentence = xSentence & " and " & Trim(.Cells(xr, 1)) & "...."
End If
End If
Next
End With
MsgBox xSentence
End Sub


--
Cheers
Nigel



"Ariel" wrote in message
...
I am trying to write a macro that will insert values from different
cells
into a sentence. The problem is that not all cells will have a value in
them.
Do you have a suggestion that does this? Here's what I mean as an
example:

Cell A1: paint
Cell B1: nails
Cell C1: null
Cell D1: screws
Cell E1: null


Here is the sentence:

This store carries the following goods: paint, nails, and screws....

As you can see from the example, I need to skip the cells with the null
values.

Any help would be much appreciated.