How to swap rows or columns in Excel?
Sometimes, when we use a worksheet, we need to swap the rows or the columns. For example, I want to exchange column A with column D, the first thing that came to my mind is to use copy and paste command. In fact, except the copy and paste, there are some other methods to solve this problem.
Swap rows or columns with copy and paste;
Swap rows or columns with VBA code;
To swap rows or columns quickly and conveniently with Kutools for Excel.
Kutools: 120 Powerful New Features for Excel. Office Tab: Enable Tabbed Editing and Browsing in Office, Just Like Chrome, Firefox, IE 8/9/10.
Classic Menu for Office: Brings Back Classic Menus and Toolbars to Office 2013, 2010 and 2007.
Swap rows or columns with copy and paste
With this method, you can swap the columns as the following steps: (in this example, I will swap column A with column D.) See screenshot:
1. First, insert a new column E behind column D, then highlight column A and copy it.
2. Then select the first cell of column E, right click and choose paste, there will be a new Date column. See screenshot:
3. Then select column A, right-click, choose Clear Contents from the context menu, and then copy and paste column D to column A. Finally, delete column D. And the two columns have been exchanged.
So many copy and paste will make you mess. The following two methods also can help you deal with this problem.
Swap rows or columns with VBA code
1. Select the two entire columns or rows that you want to swap with holding the Ctrl key.
2. Then press Alt + F11 or clickclick Developer > Visual Basic, and a new Microsoft Visual Basic for applications window will display, click Insert > Module, and then input the following codes in the module.
-
Swap Columns Code
-
Swap Rows Code
Sub swapcolumns()
Dim xlong As Long
If Selection.Areas.Count <> 2 Then
MsgBox "Must have exactly two areas for swap." & Chr(10) _
& "You have " & Selection.Areas.Count & " areas."
Exit Sub
End If
If Selection.Areas(1).Rows.Count <> Cells.Rows.Count Or _
Selection.Areas(2).Rows.Count <> Cells.Rows.Count Then
MsgBox "Must select entire Columns, insufficient rows " _
& Selection.Areas(1).Rows.Count & " vs. " _
& Selection.Areas(2).Rows.Count & Chr(10) _
& "You should see both numbers as " & Cells.Rows.Count
Exit Sub
End If
Dim areaSwap1 As Range, areaSwap2 As Range, onepast2 As Range
'--verify that Area 2 columns follow area 1 columns
'--so that adjacent single column swap will work.
If Selection.Areas(1)(1).Column > Selection.Areas(2)(1).Column Then
Range(Selection.Areas(2).Address & "," & Selection.Areas(1).Address).Select
Selection.Areas(2).Activate
End If
Set areaSwap1 = Selection.Areas(1)
Set areaSwap2 = Selection.Areas(2)
Set onepast2 = areaSwap2.Offset(0, areaSwap2.Columns.Count).EntireColumn
areaSwap2.Cut
areaSwap1.Resize(, 1).EntireColumn.Insert Shift:=xlShiftToRight
areaSwap1.Cut
onepast2.Resize(, 1).EntireColumn.Insert Shift:=xlShiftToRight
Range(areaSwap1.Address & "," & areaSwap2.Address).Select
xlong = ActiveSheet.UsedRange.Rows.Count 'correct lastcell
End Sub
Sub swapRows()
Dim xlong As Long
If Selection.Areas.Count <> 2 Then
MsgBox "Must have exactly two areas for swap." & Chr(10) _
& "You have " & Selection.Areas.Count & " areas."
Exit Sub
End If
If Selection.Areas(1).Columns.Count <> Cells.Columns.Count Or _
Selection.Areas(2).Columns.Count <> Cells.Columns.Count Then
MsgBox "Must select entire Rows, insufficient columns"
Exit Sub
End If
Dim areaSwap1 As Range, areaSwap2 As Range, onepast2 As Range
'--verify that Area 2 rows follow area 1 rows
'--so that adjacent single column swap will work.
If Selection.Areas(1)(1).Row > Selection.Areas(2)(1).Row Then
Range(Selection.Areas(2).Address & "," & Selection.Areas(1).Address).Select
Selection.Areas(2).Activate
End If
Set areaSwap1 = Selection.Areas(1)
Set areaSwap2 = Selection.Areas(2)
Set onepast2 = areaSwap2.Offset(areaSwap2.Rows.Count, 0).EntireRow
areaSwap2.Cut
areaSwap1.Resize(1).EntireRow.Insert Shift:=xlShiftDown
areaSwap1.Cut
onepast2.Resize(1).EntireRow.Insert Shift:=xlShiftDown
Range(areaSwap1.Address & "," & areaSwap2.Address).Select
xlong = ActiveSheet.UsedRange.Columns.Count 'correct lastcell
End Sub
3. Then click 
button to run the code, the two columns or rows will be swapped.
If there is no Developer tab in your Ribbon, please click File > Options > Customize Ribbon and check Developer according to following screenshot:
These codes are very difficult for starters of Excel, is there a simple way to do it quickly and time-saving. Of course, Kutools for Excel can help you swap rows or columns quickly and comfortably.
To swap rows or columns quickly and conveniently with Kutools for Excel
Kutools for excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.
If you have installed Kutools for Excel, you can swap two columns or rows quickly.
1. Click Kutools > Range Converter > Swap ranges，see screenshot:
2. In the Swap Rangesdialog box, click 
button from Swap Range 1and select column A that you want to swap, and then click button from Swap Range 2 to select column D.( Note: The ranges for swapping must be identical in the same size.)
3. Then click OK. The two columns have been swapped. See screenshots:
With this Swap Ranges utility, you are not only can swap rows or columns but also can swap contents of two ranges. Click here to know more about this feature.
Related article:
How to quickly swap contents of two cells in Excel?
Kutools for Excel
More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.
