How do I change the selection in a For loop?

[Djbdrake]

I'm trying to put a string together, compiling the words in A1:C8, by column. The output for the first column output is A10, the output for the second column is B10 etc. and I want the selection to change each time too so that it only includes column A in the A10 output, column B in the B10 output etc. but the selection just keeps getting added instead so the first output is just column A and then the second output is column is column A and B and the third output is column A, B and C. The code I'm using at the moment is:

Sub test()
For x = 1 To 3
Range(Cells(1, x), Cells(8, x)).Select
Dim rng As Range
Dim i As String
For Each rng In Selection
i = i & rng
Next rng
Cells(10, x).Value = Trim(i)
Next x
End Sub

 

[gallen]

Seems long winded but the quick answer is you need to clear i before the next x

i = ""
Next X

 

[Yongle]

No need to select. Try this

Sub test()
    Dim rng As Range
    Dim i As String
    Dim X As Long
    For X = 1 To 3
        For Each rng In Range(Cells(1, X), Cells(8, X))
            i = i & rng
        Next rng
        Cells(10, X).Value = Trim(i)
        i = ""
    Next X
End Sub

It is easier to read your code if you click on </> (above post window) and paste your code into the window that opens up

 

[jmacleary]

Hi there. You need to clear i for each column. Try this mod:

Sub test()
For x = 1 To 3
Range(Cells(1, x), Cells(8, x)).Select
Dim rng As Range
Dim i As String
For Each rng In Selection
i = i & rng
Next rng
Cells(10, x).Value = Trim(i)
i=""
Next x
End Sub

 

[Djbdrake]

Seems long winded but the quick answer is you need to clear i before the next x

i = ""
Next X

Thanks for the, worked a treat!