I think this is what you are looking for. The loop will run from row 4 to
last row in Col. G.

Option Explicit

Public Sub ConcatenateEmail()

Dim myString As String
Dim r As Long

For r = 4 To Cells(Rows.Count, "G").End(xlUp).Row
If IsError(Cells(r, "G")) Then
Exit For
Else
myString = myString & Cells(r, "G").Value & ","
End If
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString

End Sub


--
Cheers,
Ryan


"John Pierce" wrote:

I need to make the following code work for a variable number of rows
and stop when it reaches a #VALUE! error.


Public Sub ConcatenateEmail()
Dim myString As String
Dim r As Long

myString = ""

For r = 4 To 585
myString = myString & Cells(r, "G") & ","
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString
End Sub