CODEWITHSUNDEEP
vbaexcel
knarusk
knarusk

Reputation: 9

Merge multiple column rows into a single cell

I have a column that looks like this

A
B
C

D
E

I need to turn this column into rows, each empty cell marking new row, looking like this:

ABC
DE

Is there an easy way to do this in Excel?

Upvotes: 0

Views: 866

Answers (2)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3187

My approach, uses the AREAS() method, perfect since your groups of data are separated by blank cells.

Option Explicit

Sub ColumnToRows()
Dim RNG As Range, a As Long

    Set RNG = Range("A:A").SpecialCells(xlConstants)

    For a = 1 To RNG.Areas.Count
        RNG.Areas(a).Copy
        Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll, Transpose:=True
    Next a

    Application.CutCopyMode = False
End Sub

If column A is not full of constants, you can change that to xlFormulas.

Upvotes: 1

assylias
assylias

Reputation: 328568

If you copy the code below in a module it should do what you are asking for (make sure the sheet with your data is the active sheet). There are no checks for data validity - it assumes that:

  • the sheet with data is the active sheet,
  • there is data in Column A

and it outputs the result in column B.

Option Explicit

Public Sub doIt()

   Dim data As Variant
    Dim result As Variant
    Dim i As Long
    Dim j As Long

    data = ActiveSheet.UsedRange
    ReDim result(1 To UBound(data, 1), 1 To 1) As Variant

    j = 1
    For i = 1 To UBound(data, 1)
       If data(i, 1) = "" Then
          j = j + 1
       Else
          result(j, 1) = result(j, 1) & data(i, 1)
       End If
    Next i

    With ActiveSheet
        .Cells(1, 2).Resize(UBound(result, 1), 1) = result
    End With

End Sub

Upvotes: 1

Related Questions