copy all data from non blank cells

[neveu]

hello forum,
could you please help me with the following chalenge:?

i have some data on column L to Y (displayed on rows).
i want to copy all data from the rows into cell L.

for example:

L M N O
row1 x y s
row2 a 9
row3
row4 v

the problem is that i have 70 lines and the formatting that I've programmed takes 1 minute.

is there a way to make a VBA loop to copy all the information to cell in L until it finds a space on the column

also, it should skip the blank row ( meaning if the cell from L column is blank - first cell )

any help is much appreciated

 

[JackDanIce]

Doesn't make much sense, can you provide an example of before and after?

 

[neveu]

hello Dan,

i'm soryy if my explanation where confusing.
as suggested I'm adding the images stating the before and after status.

thank you

before

Uploaded with ImageShack.us


after

Uploaded with ImageShack.us

 

[JackDanIce]

Unfortunately, I can't access those links from work. You may want to try Excel Jeanie or PM me.

 

[neveu]

Hello,
I'm not able to install any software either.
tha't why i'm using this onle solutin for print screens.

did you tried adding the link to trusted sites? for me works in most of the cases.

can you suggest another online program i can use that you can access afterwards?

 

[JackDanIce]

Try:

Sub MyConcatenate()
Dim i As Long, j As Long
Dim holdstring As String
Application.ScreenUpdating = False
j = 12
For i = 2 To Range("L" & Rows.Count).End(xlUp).row
    
    Do
        holdstring = holdstring & " " & Cells(i, j)
        j = j + 1
    Loop While Len(Cells(i, j)) > 0
    
    Cells(i, 12) = Trim(holdstring)
    Range(Cells(i, 13), Cells(i, j)).ClearContents
    
    j = 12
    holdstring = ""
    
Next i
Application.ScreenUpdating = True
End Sub