swapping 2 selected cells/ranges -vba

[lezawang]

Hi
I wrote a code to swap 2 cells. Can I swap 2 selected cells/ranges instead of hard coding the way I did it. Thank you so much

Sub myswab()
Dim tmp As Double
tmp = Range("a1").Value
Range("a1").Value = Range("b1").Value
Range("b1").Value = tmp
End Sub

 

[Akuini]

Try this:

ax = Split(Selection.Address, ",")
a = Range(ax(0))
b = Range(ax(1))
Range(ax(0)) = b
Range(ax(1)) = a

Edit: The code only work if the selected cell is non contiguous

 

[Akuini]

For 2 contiguous selected cells , use this:

Tmp = Selection.Item(1)
Selection.Item(1) = Selection.Item(2)
Selection.Item(2) = Tmp

 

[lezawang]

Thank you so much. Can you please break down this code for me. I did not understand the split function arguments. Thank you.

Try this:

ax = Split(Selection.Address, ",")
a = Range(ax(0))
b = Range(ax(1))
Range(ax(0)) = b
Range(ax(1)) = a

Edit: The code only work if the selected cell is non contiguous

 

[Akuini]

Let's say you select A1 & C1
Selection.Address will be: "$A$1,$C$1"
Spit function with comma as delimiter will split the address into "$A$1" & "$C$1"

Dim ax, a, b
ax = Split(Selection.Address, ",") ' ax will have 2 entries: ax(0) ="$A$1" & ax(1) = "$C$1"
a = Range(ax(0))
b = Range(ax(1))
Range(ax(0)) = b
Range(ax(1)) = a

Note:
-the above code work also for selection of 2 or more cells (say you select A1:A5 & C1:C5)
- ax, a, b must be variant type


Note:
The code in post #2 (for 2 contiguous selected cells) above only works for 2 selected cells, but this code below will work for 2 selected cells or more (say you select A1:B5). So use this one instead:

Dim va As Variant, z As Long
z = Selection.Rows.count
va = Selection.Item(1).Resize(z)
Selection.Item(1).Resize(z).Value = Selection.Item(2).Resize(z).Value
Selection.Item(2).Resize(z) = va

 

[Akuini]

I combined both codes so it works for selecting contiguous or non contiguous cells, and for selecting 2 or more cells.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] toSwap()
[COLOR=Royalblue]Dim[/COLOR] ax, a, b, va
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

[COLOR=Royalblue]If[/COLOR] InStr(Selection.Address, [COLOR=brown]","[/COLOR]) [COLOR=Royalblue]Then[/COLOR]
    ax = Split(Selection.Address, [COLOR=brown]","[/COLOR])
    a = Range(ax([COLOR=crimson]0[/COLOR]))
    b = Range(ax([COLOR=crimson]1[/COLOR]))
    Range(ax([COLOR=crimson]0[/COLOR])) = b
    Range(ax([COLOR=crimson]1[/COLOR])) = a
[COLOR=Royalblue]Else[/COLOR]
    z = Selection.Rows.count
    va = Selection.Item([COLOR=crimson]1[/COLOR]).Resize(z)
    Selection.Item([COLOR=crimson]1[/COLOR]).Resize(z).Value = Selection.Item([COLOR=crimson]2[/COLOR]).Resize(z).Value
    Selection.Item([COLOR=crimson]2[/COLOR]).Resize(z) = va
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Rick Rothstein]

For 2 contiguous selected cells , use this:

Tmp = Selection.Item(1)
Selection.Item(1) = Selection.Item(2)
Selection.Item(2) = Tmp

You can also do this without involving a "new" temporary variable...

Selection(1) = Selection(1) + Selection(2)
Selection(2) = Selection(1) - Selection(2)
Selection(1) = Selection(1) - Selection(2)

 

[Rick Rothstein]

I combined both codes so it works for selecting contiguous or non contiguous cells, and for selecting 2 or more cells.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] toSwap()
[COLOR=Royalblue]Dim[/COLOR] ax, a, b, va
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

[COLOR=Royalblue]If[/COLOR] InStr(Selection.Address, [COLOR=brown]","[/COLOR]) [COLOR=Royalblue]Then[/COLOR]
    ax = Split(Selection.Address, [COLOR=brown]","[/COLOR])
    a = Range(ax([COLOR=crimson]0[/COLOR]))
    b = Range(ax([COLOR=crimson]1[/COLOR]))
    Range(ax([COLOR=crimson]0[/COLOR])) = b
    Range(ax([COLOR=crimson]1[/COLOR])) = a
[COLOR=Royalblue]Else[/COLOR]
    z = Selection.Rows.count
    va = Selection.Item([COLOR=crimson]1[/COLOR]).Resize(z)
    Selection.Item([COLOR=crimson]1[/COLOR]).Resize(z).Value = Selection.Item([COLOR=crimson]2[/COLOR]).Resize(z).Value
    Selection.Item([COLOR=crimson]2[/COLOR]).Resize(z) = va
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Extending what I posted above to handle contiguous or non-contiguous selections...

Sub Test()
  If Selection.Areas.Count = 1 Then
    Selection(1) = Selection(1) + Selection(2)
    Selection(2) = Selection(1) - Selection(2)
    Selection(1) = Selection(1) - Selection(2)
  Else
    Selection.Areas(1) = Selection.Areas(1) + Selection.Areas(2)
    Selection.Areas(2) = Selection.Areas(1) - Selection.Areas(2)
    Selection.Areas(1) = Selection.Areas(1) - Selection.Areas(2)
  End If
End Sub

 

[lezawang]

Thank you so much for the explanation. So clear now but just last question please, why ax,a,b have to be variant? is that because they will be array if user select lets say a1:a10? Thank you once again

 

[Akuini]

You can also do this without involving a "new" temporary variable...

Selection(1) = Selection(1) + Selection(2)
Selection(2) = Selection(1) - Selection(2)
Selection(1) = Selection(1) - Selection(2)

Hi, Rick Rothstein
Thanks for the code.
I didn't know we can use index for selection.
So is there any difference between "Selection.Item(1)" & "Selection(1)"?

I'm asking more for dealing with non contiguous range. Say I select A1 & C1, then I thought Selection.Item(2) will be C1, but actually it's A2.
So how can we get to C1 using an index of selection (or something else)?