Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Swapping Two Numbers.

Swapping Two Numbers

Written by Allen Wyatt (last updated March 10, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


16

If you do any serious macro programming, there will eventually come a time when you want to swap the values in two numeric variables. In some versions of BASIC, there are commands that handle this. VBA leaves you to our own devices, however. The following technique should do the trick for most people:

    TempNum = MyNum1
    MyNum1 = MyNum2
    MyNum2 = TempNum

When completed, the values in MyNum1 and MyNum2 have been swapped, and TempNum doesn't matter since it was intended (by this technique) as a temporary variable anyway.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9098) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Swapping Two Numbers.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Shading Table Rows

Need to format the rows of a table so that your data is showcased better? Here are a few ways you can get the shading you ...

Discover More

Inserting the Document Revision Number

Need to know how many times your document has been saved? Word keeps track of this information, and makes it easily ...

Discover More

Canceling an Edit

When editing a cell, you may want to cancel the edit at some point. There are two ways to do this, both described in this ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time ...

Discover More

Renaming a Macro

Got a macro that doesn't have quite the right name? You can rename the macro by following these simple steps.

Discover More

Replacing Some Formulas with the Formula Results

Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six minus 4?

2021-02-21 10:47:33

J. Woolley

Although this Tip pertains to swapping values between two VBA variables, you might also be interested in swapping two worksheet cells. My freely available SwapTwoCells macro will swap the cells in Selection using Copy/Paste with Paste options and support for Undo plus Redo/Repeat. I use it with shortcut key Ctrl+Shift+S. See My Excel Toolbox: https://sites.google.com/view/MyExcelToolbox/


2021-02-20 17:12:13

John Mann

I don't know if this will help anyone. Back in the dim and distant past I worked as an electronic technician, and often worked with digital circuits, including IC's containing various logic circuits, including XOR gates.

The term XOR is a contraction of Exlusive OR

The truth table for the XOR function is as follows (A & B are inputs, O/P is the resulting output).

A B = O/P

0 0 0
1 0 1
0 1 1
1 1 0

In words, the output is 1 (TRUE) if either of the the inputs is 1 (TRUE), but only one of the imputs is 1(TRUE).. This differes from the simple OR gate where the output is 1 (TRUE) if either or BOTH inputs are 1 (TRUE)

In my day, when performed with multiple bit words, the logic operation was done on a bit-wise manner.


2019-03-20 05:05:59

Alan Elston

Hi Peter,
So it seems that in a ……_
=Number1 Xor Number2
_........ operation, this happens:
_ First the two “normal” ( decimal ) numbers are converted to their Binary ( 0 , 1 type) representation.
_ Then some specific Boolean logic is applied to those binary numbers( as shown at the link you gave ). That logic returns a different binary output.
_ That binary output is then converted back to a normal , ( decimal ) , number

It seems that the result of that logic just happens to do a swap on numbers when used as Rick showed: The Xor is a specific “Boolean Binary Computer thingy” ….. if you give it decimal numbers than, rather than crashing, it tries to convert them to their binary equivalent, then does the binary Boolean logic on that. I am not sure why it then decides to convert its answer back to a decimal. Maybe that is specific to the VB version of the Xor

I don’t feel too comfortable with the Xor thing, I am not sure why. In any case I usually sort and swap things that are a mixture of text and numbers, so it is of less use to me as it seems only to work on numbers
I feel comfortable for now with the use of a temporary variable way.

But another interesting thing to know about…
:)


2019-03-19 20:53:27

Peter Atherton

Xor Operator
Microsoft doc on Xor Operator is @
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/xor-operator
There is also the Xand Operator. However I remember reading somewhere that the are not often used because both varaibles are always evaluated wherther or not that the second variable is necessary..
Anyway I ran a few more test with the previus macro and realised that the first calc for a is either A + B or B - A. Here is a list of a few swaps.

Swap A with B
A= 4 B= 56
a= 60 'A + B
B= 4 'B= A
A= 56 'A= a - B

Swap A with B
A= 12 B= 13
a= 1 'B - A
B= 12 'B = A
A= 13 'A = a + b

Swap A with B
A= 10 B= 110
A= 100 'a= B - A
B= 10 'B- a
A= 110 'A = B + a

Swap A with B
A= 64 B= 23
a= 87 'a = A + B
B= 64 'B = A
A= 23 'A = a - B


2019-03-19 12:34:59

Peter Atherton

Alan and Willy

I tried my own version but still can't quite see the logic

Sub test3()
Dim a, b
Set a = [d3]
Set b = [E3]
Debug.Print "Swap A with B"
Debug.Print "A= " & a & vbTab & "B= " & b
a.Value = a Xor b
Debug.Print "A= " & a
b.Value = a Xor b
Debug.Print "B= " & b
a.Value = a Xor b
Debug.Print "A= " & a
End Sub

Results
Swap A with B
A= 67 B= 4
A= 71
B= 67
A= 4

Mind you as Rick said, it is easy to remember


2019-03-19 05:19:06

Alan Elston

Thanks Willy.
I see from what you did that it works. I am still puzzled how. I expect it is just to do with basic Computer logic that I never learnt ( yet ).
:)


2019-03-18 12:17:38

Willy Vanhaelen

@Alan
I also am puzzled by this elegant solution. So I have been experimenting a bit and although I don't fully understand the working, playing with the following macro is quite interesting.

Sub test()
A = 1
B = 2
A = A Xor B
Debug.Print A & "|" & B
B = A Xor B
Debug.Print A & "|" & B
A = A Xor B
Debug.Print A & "|" & B
End Sub

Result:
3|2
3|1
2|1

I also discovered that you can use XOR for rounding a decimal number:

Sub test2()
A = 1.49
'A = 1.5
Debug.Print A Xor 0
End Sub

Result:
A = 1.49 -> 1
A = 1.5 ->2

Have fun :-)


2019-03-14 02:56:53

Alan Elston

Thanks Michael,
Rick Rothstein has also mentioned that Xor solution over at Allen’s Excel Tip page
h t t p s : / / excel.tips.net/T002525_Swapping_Two_Numbers
I can’t use it yet because I only use things I understand. …_
_... I haven’t found an explanation on the internet simple enough for a computer idiot like me to understand yet :(
Alan Elston


2019-03-13 13:23:46

MIchael Armstrong

There's a very cool algorithm for swapping binary numbers called the XOR Swap which needs no intermediate storage area. Since all Excel values are stored as binary numbers, it would seem you could use this algorithm somehow. Wkipedia has a good article on it, but in summary, if you have two variables X and Y, then to swap them:

X := X XOR Y
Y := Y XOR X
X := X XOR Y

... and Viola!


2019-03-12 13:54:38

Peter Atherton

Thomas Papavasiliou

Thanks Thomas, I had realized that in case of swapping rows and columns didn't work. Here is my solution, at least the loop is only used in that case.

Sub swapRanges()
Dim x As Variant, y As Variant, i As Long
Dim rng1 As Range, rng2 As Range, nr
If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count <> rng2.Cells.Count Then
Exit Sub
End If
If rng1.Rows.Count = rng2.Columns.Count Then
nr = rng1.Rows.Count
ReDim x(1 To nr)
ReDim y(1 To nr)

For i = 1 To nr
x(i) = rng1(i)
y(i) = rng2(i)
Next

For i = 1 To nr
rng2(i) = x(i)
rng1(i) = y(i)
Next

Else
x = [rng1]
rng1.Value = rng2.Value
rng2 = x
End If
End Sub


2019-03-12 04:39:45

Thomas Papavasiliou

To Peter Atherton

Thanks for your elegant approach as stated in "Sub swapRanges2()".

I dare to propose another test to avoid cases that have the same number of cells but a different number of rows and columns as for example 4 rows by 3 columns and 3 rows by 4 columns.
As we all know, macro results cannot be undone and if macro runs in such a case, we get some #NA errors.
I think a single test for identical rows (or columns), preceding the test for the identical number of cells, will avoid such potential errors.


2019-03-11 13:50:46

Peter Atherton

On further thoughts, there is no need to have two arrays, one will do; just like a sort algotithm.

Sub swapRanges2()
Dim x As Variant
Dim rng1 As Range, rng2 As Range
If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count = rng2.Cells.Count Then
x = [rng1]
rng1.Value = rng2.Value
rng2 = x
End If
End Sub


2019-03-11 12:55:28

Peter Atherton`

Jim

I liked your macro and decided to try my own. The following creates ranges for both areas and reads the ranges into arrays. The arrays are copied into each others range. So no looping.

Sub swapRanges()
Dim x As Variant, y As Variant
Dim rng1 As Range, rng2 As Range

If Selection.Areas.Count <> 2 Then
Exit Sub
Else
Set rng1 = Range(Selection.Areas(1).Address)
Set rng2 = Range(Selection.Areas(2).Address)
End If

If rng1.Cells.Count = rng2.Cells.Count Then
x = [rng1]
y = [rng2]
End If
rng1 = y
rng2 = x
End Sub


2019-03-11 09:55:17

Jim Wessner

DISLAIMER: I cannot find the original source for the code below, so I cannot give proper credit to the originator.

Quite a few years I decided there must be a way to swap two cells and found several methods similar to that in this article. One day I stumbled across the VBA code below which allows me to swap two ranges of cells, as long as they are the same configuration of cells. I was using this macro so often (and still do to this day) that I placed an icon for it in the QAT.

Sub SwapRange()
Dim arr1()
Dim arr2()
Dim Counter As Long
Dim CellCount As Long

If Selection.Areas.Count <> 2 Then
MsgBox "You must have two areas selected", vbCritical, "Aborting"
Exit Sub
ElseIf Selection.Areas(1).Cells.Count <> Selection.Areas(2).Cells.Count Then
MsgBox "Your selections must have the same number of cells", vbCritical, "Aborting"
Exit Sub
End If

CellCount = Selection.Areas(1).Cells.Count
ReDim arr1(1 To CellCount)
ReDim arr2(1 To CellCount)

For Counter = 1 To CellCount
arr1(Counter) = Selection.Areas(1).Cells(Counter)
arr2(Counter) = Selection.Areas(2).Cells(Counter)
Next

For Counter = 1 To CellCount
Selection.Areas(1).Cells(Counter) = arr2(Counter)
Selection.Areas(2).Cells(Counter) = arr1(Counter)
Next

End Sub


2019-03-10 12:16:50

Allen

Thanks for the feedback, Ron. I made that change.

-Allen


2019-03-09 21:35:25

Ron S

Hey Allen:
I like the abstracts you use in the newsletter.

In the newsletter there is a minor typo in the abstract. It says:
<snip>
want to exchange the values held by two macros.
</snip>

I'm sure you meant to say
<snip>
want to exchange the values held by two VARIABLES.
</snip>

PS: I liked it better when you used to include those abstracts in the articles also. They provide a good intro.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.