Convert Columns And Rows Into Single Column in Excel

  Transpose/Convert Columns And Rows Into Single Column With VBA Code

With the following VBA code, you can also join the multiple columns and rows into a single column.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Sub ConvertRangeToColumn() 'Updateby20131126 Dim Range1 As Range, Range2 As Range, Rng As Range Dim rowIndex As Integer xTitleId = "KutoolsforExcel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8) rowIndex = 0 Application.ScreenUpdating = False For Each Rng In Range1.Rows     Rng.Copy     Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True     rowIndex = rowIndex + Rng.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

3. Press F5 key to run the code, and a dialog is displayed for you to select a range to convert. See screenshot:

4. Then click Ok, and another dialog is displayed to select a singel cell to put out the result, see screenshot:

5. And click Ok, then the cell contents of the range are converted to a list of a column, see screenshot: