How to Swap Two Columns or Rows in Excel


This post will guide you how to swap rows or columns in Excel. How do I swap cell values between rows or columns in Excel. How to swap columns or rows with VBA Macro in Excel. How to swap columns in Excel. How to swap two range of cells with VBA macro in Excel.

1. Swap Two Columns

If you want to swap two ranges in different columns or rows in Excel, you can write down an Excel VBA Macro to quickly swap the selected two ranges. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub SwapRange()
Dim Range1 As Range
Dim Range2 As Range
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("select the first range:", "SwapRange", Range1.Address, Type:=8)
Set Range2 = Application.InputBox("select the second range:", "SwapRange", Type:=8)
tmp1 = Range1.Value
tmp2 = Range2.Value
Range1.Value = tmp2
Range2.Value = tmp1
End Sub
swap two ranges1

#5 back to the current worksheet, then run the above excel macro. Click Run button.

swap two ranges2

#6 select the first range, such as: A1:A3

swap two ranges3

#7 select the second range, such as: B1:B3

swap two ranges4

#8 let’s see the result.

swap two ranges5

2. Video: Swap Two Columns

This Excel video tutorial, we’re going to learn how to swap two columns or rows in Excel using a VBA macro. This is a quick and efficient way to rearrange your data without manually cutting and pasting.

Leave a Reply