**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.

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

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**.

**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!

One of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this ...

Discover MoreIt is often helpful to get user input within a macro. Here's a quick way to present some options and get the user's response.

Discover MoreWhen developing a macro, it is often necessary to step through the various code lines so you can see what is happening on ...

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

2021-02-21 10:47:33

J. Woolley

2021-02-20 17:12:13

John Mann

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

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

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

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

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

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

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

X := X XOR Y

Y := Y XOR X

X := X XOR Y

... and Viola!

2019-03-12 13:54:38

Peter Atherton

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

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

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`

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

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

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.

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments