Macro To Combine Rows Into 1 through unknown # of rows

[gmercer82]

I would like to have a macro that will take all of the rows in Column A and combine them into cell B2. Normally in excel I would just do A1&A2&A3...

Information Example:
St Louis
Chicago
Boston

In cell B2 I would like it to look like
St LouisChicagoBoston

I was able to get a formula that will run it through the number of rows that will have something. I just need to code for the space listed as [needed formula].

Dim WS As Worksheet
Dim LastRow As Long
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For A = 1 To LastRow

[needed formula]

Next
End With

 

[Joe4]

Try this:

Dim WS As Worksheet
Dim LastRow As Long
Dim myValue as String

 
Set WS = ActiveSheet
With WS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    For A = 1 To LastRow
        myValue=myValue & Cells(A,1)
    Next A
    Range("B2")=myValue
End With

 

[gmercer82]

Thank-you.

 

[texasalynn]

try this

Sub CombineDataA()
Dim LR As Long, i As Long
Dim CData As String
LR = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If i = 2 Then
        CData = Cells(i, "A")
    End If
    If i > 2 Then
        CData = CData & Cells(i, "A")
    End If
Next i
Range("B2") = CData
End Sub