VBA several columns into 1 column

[neveu]

hello Forum,

could you please advise how to adress the next issue?
i have several columns with data ( up to 50) starting with column M.
each column has a different number of rows. ( from 10 up to 30)

i would need your help to consolidate all this columns into a single column: let's say column K.

thank you in advance

 

[ayazgreat]

If your data starts from Column K then in which column do you want your data to be consolidated.

 

[neveu]

hello,

my data starts in column M.
i want it to be consolidated in column K.

 

[ayazgreat]

Ok, now let me know something more that has your data header and from which row your data starts? After consoldating the data you want to delete those columns ? (From col m to onword)

 

[neveu]

the data starts from row 1 and yes i would need to delete the columns after being consolidated

 

[ayazgreat]

Try this

Sub MoveAllDataToColumnK()
    Dim i As Long, ws As Worksheet, rngCopy As Range, rngEnd As Range
    Set ws = ActiveSheet
    Do Until ws.Cells(1, 13).Value = ""
        Set rngCopy = ws.Range("M1", ws.Cells(ws.Rows.Count, "M").End(xlUp))
        Set rngEnd = ws.Cells(ws.Rows.Count, "K").End(xlUp).Offset
        rngEnd.Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value
        rngCopy.EntireColumn.Delete
    Loop
End Sub

 

[neveu]

thank you very much.,
it works perfect