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

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

New, but similar, problem. I would like to put the cell contents of
Col F, from Row 4 to the first Error (#VALUE!) into an array and then
write the array into Col A of another sheet. Capeesh?

This code will loop 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

Hope this helps! If so please let me know by clicking "YES" below.
--
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

You don't have to fill an array to accomplish what you need. I added one
line to the code I gave you earlier which will do what your second question
is asking. You may need to adjust the sheet name. I assumed that the sheet
you are copying too has a header.

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
Range("F4:F" & r).Value = Sheets("Sheet2").Range("A2:A" &
r).Value
Exit For
Else
myString = myString & Cells(r, "G").Value & ","
End If
Next r

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

End Sub

Hope this helps! If so, then click "YES" below. Thanks!
--
Cheers,
Ryan


"RyanH" wrote:

This code will loop 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

Hope this helps! If so please let me know by clicking "YES" below.
--
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

Thanks, Ryan, but as I said the problem has changed. I don't want to
concatenate the cell contents into a single string. I need them listed
on the other sheet in a column. The difference is that on sheet 1, col
F is formulas and on Sheet 2, col A, I want to 'paste special' the
values for further work. For that, I think capturing it all in an
array and then writing it all at once be most efficient.

I am confused on what you are needing. Please explain in detail and give an
example of what you are wanting.
--
Cheers,
Ryan


"John Pierce" wrote:

Thanks, Ryan, but as I said the problem has changed. I don't want to
concatenate the cell contents into a single string. I need them listed
on the other sheet in a column. The difference is that on sheet 1, col
F is formulas and on Sheet 2, col A, I want to 'paste special' the
values for further work. For that, I think capturing it all in an
array and then writing it all at once be most efficient.

The project is this:
1. Import a file consisting of one continuous string of hundreds of e-
mail addresses.
2. Parse the string into one address per line and clean out junk
3. Eliminate duplicates
4. Fix broken addresses: missing “@” or “.”
5. Reconcatenate for reuse.
You helped me get from step 2 to step 5, but I had neglected steps 3
and 4. So now I want to have the parsed addresses, (which are on
Sheet1, Col F, in the form of formulas) copied to another sheet as
values (text) so I can edit them.