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):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD]j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]u[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]w[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












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):
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]e[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
[TR]
[TD]g[/TD]
[/TR]
[TR]
[TD]h[/TD]
[/TR]
[TR]
[TD]i[/TD]
[/TR]
[TR]
[TD]j[/TD]
[/TR]
[TR]
[TD]k[/TD]
[/TR]
[TR]
[TD]l[/TD]
[/TR]
[TR]
[TD]m[/TD]
[/TR]
[TR]
[TD]n[/TD]
[/TR]
[TR]
[TD]o[/TD]
[/TR]
[TR]
[TD]p[/TD]
[/TR]
[TR]
[TD]q[/TD]
[/TR]
[TR]
[TD]r[/TD]
[/TR]
[TR]
[TD]s[/TD]
[/TR]
[TR]
[TD]t[/TD]
[/TR]
[TR]
[TD]u[/TD]
[/TR]
[TR]
[TD]v[/TD]
[/TR]
[TR]
[TD]w[/TD]
[/TR]
[TR]
[TD]x[/TD]
[/TR]
[TR]
[TD]y[/TD]
[/TR]
[TR]
[TD]z[/TD]
[/TR]
</tbody>[/TABLE]


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