Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 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

by Allen Wyatt
(last updated March 10, 2019)

14

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 Office 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

Copying Formulas using a Pattern

Copying formulas from one cell to another is quite intuitive in Excel—unless you want the copied formulas to follow ...

Discover More

Printing AutoCorrect Entries

If you want to print a list of all the AutoCorrect entries in your document, Word doesn't provide a method. You can use ...

Discover More

Excel 2010 Filters and Filtering (Table of Contents)

Excel provides two ways to filter your data so that only what you want to see is displayed. Discover how filtering works ...

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)

Deleting Zero Values from a Data Table

Want to get rid of all the zero values in a range of cells? This tip provides a couple of different ways you can ...

Discover More

Converting HSL to RGB

When working with colors in Excel you can specify them using either RGB or HSL values. Converting from HSL to RGB can be ...

Discover More

Automatically Enabling Macros for Specific Workbooks

On your system you may have workbooks that contain macros you know are safe to use. Microsoft provides two things you can ...

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}] 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 1 + 3?

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.