I have 3 columns:
-------------------
a D g
b E h
c F
and would like to have them aligned into 1 single column:
--------------------------------------------------------------
a
b
c
D
E
F
g
h
Any suggestion formulas, functions, etc. for what i could best use for this purpose.
In reality, i have about 200 columns with different number of rows.
So i need to align smarter than manually.
Re: Align several columns into 1 columns
Try this:
Option Explicit
Sub ams526()
Dim lr As Long, i As Long, lra As Long
Dim lc As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To lc
lr = Cells(Rows.Count, i).End(xlUp).Row
lra = Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(1, i), Cells(lr, i)).Copy Range("A" & lra + 1)
Application.CutCopyMode = xlCopy
Next i
End SubRe: Align several columns into 1 columns
Another way with formulas.
A helper column in B with this array-entered formula in B5 and filled down until you get blanks.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)
Then this regular formula in A5 filled down until you get blanks.
[Table="class: grid"]
[td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td]
[td="bgcolor: #DCE6F1"]
1
[/td]
a
D
g
[td="bgcolor: #DCE6F1"]
2
[/td]
b
E
h
[td="bgcolor: #DCE6F1"]
3
[/td]
c
F
[td="bgcolor: #DCE6F1"]
4
[/td]
[td="bgcolor: #DCE6F1"]
5
[/td]
a
1
[td="bgcolor: #DCE6F1"]
6
[/td]
b
1
[td="bgcolor: #DCE6F1"]
7
[/td]
c
1
[td="bgcolor: #DCE6F1"]
8
[/td]
D
2
[td="bgcolor: #DCE6F1"]
9
[/td]
E
2
[td="bgcolor: #DCE6F1"]
10
[/td]
F
2
[td="bgcolor: #DCE6F1"]
11
[/td]
g
3
[td="bgcolor: #DCE6F1"]
12
[/td]
h
3
[td="bgcolor: #DCE6F1"]
13
[/td]
[/table]
Don’t have an account yet? Register yourself now and be a part of our community!