VBA for Combing all data into a single column

[Alex0013]

Hi All,

This may already be answered on here, but couldn't find exactly what I was looking for...

I want to take this, data pasted into Excel as rows, with varying lengths of columns (can assume I paste into Column B):

a	b	c
d	e	f	g	h
i	j
k	l	m	n	o	p	q	r	s	t
u
v
w	x	y	z

<tbody>
</tbody>

And convert to this, a single column with all the data and no blank rows (Can assuming I want this in the same sheet in Column A):

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

<tbody>
</tbody>

Any help on this would be amazing!
Thanks!

Excel 2016 64-bit

 

[Fluff]

How about

Sub TransposeAll()
   Dim Iary As Variant, Oary As Variant
   Dim r As Long, c As Long, i As Long
   
   Iary = Range("B1").CurrentRegion.value2
   ReDim Oary(1 To UBound(Iary) * UBound(Iary, 2), 1 To 2)
   
   For r = 1 To UBound(Iary)
      For c = 1 To UBound(Iary, 2)
         If Iary(r, c) <> "" Then
            i = i + 1
            Oary(i, 1) = Iary(r, c)
         End If
      Next c
   Next r
   Range("A1").Resize(i).Value = Application.Index(Oary, 0, 1)
End Sub

 

[Alex0013]

That worked amazingly, thank you very much!

 

[gallen]

I have no clue what Fluff has written and I bow to his/her expertise.

However I've written this so I'll post it

Sub OneCol()
    Dim c As Range
    Dim col As New Collection
    
    'get all cell values into a collection
    For Each c In Me.UsedRange
        If c <> "" Then col.Add (c)
    Next c
    
    'clear the sheet
    Me.UsedRange.Clear
    
    'write all values back to col A
    For i = 1 To col.Count
        Range("A" & i) = col(i)
    Next i
    
End Sub

 

[JLGWhiz]

another method

Sub t()
Dim c As Range
With ActiveSheet
    Columns(1).Insert
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
            If c <> "" Then
                .Range(c, .Cells(c.Row, Columns.Count).End(xlToLeft)).Copy
                .Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
            End If
        Next
        .UsedRange.Offset(, 1).ClearContents
End With
End Sub

 

[Fluff]

That worked amazingly, thank you very much!

You're welcome