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:
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!
Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time ...
Discover MoreGot a macro that doesn't have quite the right name? You can rename the macro by following these simple steps.
Discover MoreMacros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...
Discover MoreFREE 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
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
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
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
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.
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