Excel Macro: Convert Multiple Rows to Columns (3 Examples)

In this article, we’re going to show you 3 methods to convert multiple rows to columns in Excel Macro. For this purpose, we’ve taken a dataset with 3 rows: “Name”, “Car Model”, and “Car Maker”.

convert multiple rows to columns in excel macro


Download Practice Workbook


3 Examples to Convert Multiple Rows to Columns in Excel

1. Excel Macro to Convert Multiple Rows to Columns in the Same Sheet

For the first method, we’re going to convert multiple rows to columns in the same Sheet using Excel VBA. Moreover, this method will keep the format of the cells intact in our rows. We’ll ask the user to input the data range to convert, and then define the output location. Without further ado, let’s jump into the action.

Steps:

  • Firstly, from the Developer tab >>> select Visual Basic.

The Visual Basic window will appear.

  • Finally, from Insert >>> select Module.

This will bring up the Module window.

convert multiple rows to columns in excel macro

  • Then, type the following code into the Module Window.
Sub ConvertRowsColumns()
    Dim cRange As Range
    Dim xRange As Range
    Set cRange = Application.InputBox(Prompt:="Please select the range to convert", Title:="Convert Rows to Columns", Type:=8)
    Set xRange = Application.InputBox(Prompt:="Select the cell where you want to start the conversion", Title:="Convert Rows to Columns", Type:=8)
    cRange.Copy
    xRange.Select
    Selection.PasteSpecial Paste:=xlPasteAll, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure ConvertRowsColumns.
  • Secondly, we’re declaring two variables as Range types.
  • Thirdly, We’re using the Application.InputBox inside the Set Statement for user input.
  • Then, we’re copying our source using the Range.Copy method.
  • After that, we’re defining the output destination using the Range.Select method.
  • Then, we’re pasting the values and formats using the Range.PasteSpecial method.
  • Moreover, we’re changing these properties –
    • Paste:=xlPasteAll -> paste everything from the selection.
    • SkipBlanks:=False -> we will not skip blank cells.
    • Transpose:=True -> we’ll transpose the selected range.
  • Finally, we’re setting the Application.CutCopyMode as False. This will cancel the cut and copy mode.

  • After that, Save and close the Module.

Now, we’re going to bring up the Macros dialog box. To do that –

  • From the Developer tab >>> select Macros.

convert multiple rows to columns in excel macro

This will bring up the Macro dialog box.

  • Then, select “ConvertRowsColumns” and press Run.

The input dialog box will appear.

  • Then, select the cell range B4:H6.
  • Press OK.

convert multiple rows to columns in excel macro

Now, we’ll need to select the output location.

  • Select cell B8 as our starting point for the output.
  • Finally, press OK.

Thus, we’ve converted multiple rows to columns in Excel.

convert multiple rows to columns in excel macro

Read More: How to Transpose Column to Multiple Rows in Excel (6 Methods)


2. Embed Macro to Convert Multiple Rows to Columns in Different Sheet

In this section, we’re going to use a For Next Loop to convert multiple rows to columns. If we use this method, we can’t keep the formatting as it is. In order to solve that, we’ll be using the Range.Borders property and Range.PasteSpecial method.

Steps:

Sub ConvertRowsColumns2()
Dim OriginalSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim cCol As Integer
Dim cRow As Integer
Set OriginalSheet = ThisWorkbook.Sheets("Source")
Set DestinationSheet = ThisWorkbook.Sheets("Destination")
DestinationSheet.Cells.ClearContents
OriginalSheet.Activate
cCol = WorksheetFunction.CountA(Range("B4:H4")) + 1
cRow = WorksheetFunction.CountA(Range("B4:B6")) + 3
For c = 1 To cCol
    For r = 3 To cRow
    DestinationSheet.Cells(c + 2, r - 2) = OriginalSheet.Cells(r, c)
    Next r
Next c
DestinationSheet.Activate
'We're gonna format the cells here
Worksheets("Destination").Range("B5:D10").Borders.LineStyle = xlContinuous
Worksheets("Source").Cells(4, 2).Copy
For x = 2 To 4
Worksheets("Destination").Cells(4, x).PasteSpecial Paste:=xlPasteFormats
Next x
Application.CutCopyMode = False
End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure ConvertRowsColumns2.
  • Secondly, we’re declaring, two variables as Worksheet and another two variables as Integer types.
  • Thirdly, we’re using the Set Statement to define our input and output Sheets.
  • Then, we’re clearing our output Sheet using the ClearContents method.
  • If we change our source dataset, then there will be no residue of the previous iteration on the output.
  • After that, we’re activating our “SourceWorksheet using the Worksheet.Activate method.
  • Then, we’re counting the number of columns and rows using the  “cCol” and “cRow” variables.
  • Then, we’re using the For Next Loop to input the values into the “DestinationSheet.
  • After that, we’re adding borders to the cells using the Borders.LineStyle property and keeping the formats as it is with the Range.PasteSpecial method.

  • Secondly, Save and close this Module.
  • Thirdly, bring up the Macro dialog box.
  • Finally, select “ConvertRowsColumns2” and press Run.

convert multiple rows to columns in excel macro

By doing so, we’ll see the output in the “DestinationSheet.

Read More: How to Transpose Multiple Columns to Rows in Excel


Similar Readings


3. Excel Macro to Convert Multiple Rows to Single Column

For the last method, we’re going to convert multiple rows to a single column. We’ll keep the cell formats intact, and we’ll use InputBox for both input and output. Moreover, we’ve adjusted our dataset a little bit.

convert multiple rows to columns in excel macro

Steps:

  • Firstly, bring up the Module window and type the following code.
Sub ConvertToSingleColumn()
Dim cRange1 As Range, cRange2 As Range, xRange As Range
Dim rNumber As Integer
xTitleId = "Convert Rows to Single Column"
Set cRange1 = Application.Selection
Set cRange1 = Application.InputBox("Source:", xTitleId, cRange1.Address, Type:=8)
Set cRange2 = Application.InputBox("Select Output:", xTitleId, Type:=8)
rNumber = 0
Application.ScreenUpdating = False
For Each xRange In cRange1.Rows
    xRange.Copy
    cRange2.Offset(rNumber, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rNumber = rNumber + xRange.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

VBA Code Breakdown

  • Firstly, we’re calling our Sub Procedure ConvertToSingleColumn.
  • Secondly, we’re declaring three variables as Range types and one variable as Integer types.
  • Thirdly, we’re using the Set Statement to define our cRange variables.
  • Then, we’re setting the Application.ScreenUpdating property to False.
  • After that, we’re using the For Next Loop to cycle through our input range.
  • Finally, We’re using the WorksheetFunction.Transpose method for our main operation here

  • Secondly, Save and close this Module.
  • Thirdly, bring up the Macro dialog box.
  • Finally, select “ConvertToSingleColumn” and press Run.

convert multiple rows to columns in excel macro

The input dialog box will appear.

  • Then, select the cell range B4:E6.
  • Press OK.

Now, we’ll need to select the output location.

  • Select cell B8 as our starting point for the output.
  • Finally, press OK.

convert multiple rows to columns in excel macro

In conclusion, we’ve shown you a method of converting multiple rows to a single column in Excel.

Read More: How to Switch Rows and Columns in Excel (5 Methods)


Practice Section

We’ve provided practice datasets for each method in the Excel file.

convert multiple rows to columns in excel macro


Conclusion

We’ve shown you 3 methods to convert multiple rows to columns in Excel Macro. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!


Further Readings

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo