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
>

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
>

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?

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.