append multiple columns

[Tony S.]

I have a time consuming task and was trying to find a way to append multiple
columns of data (with blank cells) under column "A". Currently, my columns
have data out to column "M", but this may increase or decrease.

From:
A B C etc. Thru Column "M"
1 Data1
2 Data 2
3 Data 3 Data X
4 Data 4 Data Z
5 Data 5
6 Data 6 Data Y

Into:
A B C etc. Thru Column "M"
1 Data1
2 Data 2
3 Data 3
4 Data 4
5 Data 5
6 Data 6
7 Data X
8 Data Y
9 Data Z
10 ect.

 

[Luke M]

Right click on sheet tab, view code. Paste this in, and run:

Sub Rearrange()
i = 1

'Runs from column A to M
For xColumn = 1 To 13
For xRow = 1 To Me.Cells(Me.Rows.Count, xColumn).End(xlUp).Row
If Not IsEmpty(Me.Cells(xRow, xColumn)) Then
'Alternatively, use this line:
'If Me.Cells(xRow, xColumn) <> "" Then
Cells(i, 1) = Me.Cells(xRow, xColumn).Value
i = i + 1
End If
Next xRow
Next xColumn
End Sub

 

[Tony S.]

That's a nice routine Luke. It works great. One question though... Is there a
way to modify the program to cut the data in columns B-M rather than copying
it, or do I need to delete that data manually after the program runs?