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]

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

Yes, they will be array. But it won't work if you just select a1:a10 because that's only one column.
Note:
- the code also works for text not just for number.
- for non contiguous selection you can select different rows for the 2 areas selected, say you select A1:A3 & B5:B7.

 

[MARK858]

. Say I select A1 & C1, then I thought Selection.Item(2) will be C1, but actually it's A2.

Although in the examples given the ranges are contiguous perhaps the below link will clarify a bit as the behavior is the same as referenced in the quote

It is not necessary for the cell to be within the range in order to be referenced this way.

http://www.cpearson.com/excel/cells.htm

 

[Rick Rothstein]

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)?

As far as I know, Selection(1) is just a shortcut notation for Selection.Item(1), so to the best of my knowledge, there is no difference between them other then their length. The indexing applies to a single range... when you select non-contiguous cells (or ranges), you are selecting individual contiguous ranges one at a time... each one those contiguous ranges is called an Area. Areas are indexed (1, 2, 3, etc.) and the individual cells of each area's range (remember, it is contiguous) can be indexed within that range only... cell indexes cannot cross from one area to another area. Hopefully that all made sense (in re-reading it, I am not sure how clear it is)... if not, write back and I will try to explain it with different wording.

 

[Akuini]

Although in the examples given the ranges are contiguous perhaps the below link will clarify a bit as the behavior is the same as referenced in the quote
http://www.cpearson.com/excel/cells.htm

Thanks for the link Mark, that's very helpful.

As far as I know, Selection(1) is just a shortcut notation for Selection.Item(1), so to the best of my knowledge, there is no difference between them other then their length. The indexing applies to a single range... when you select non-contiguous cells (or ranges), you are selecting individual contiguous ranges one at a time... each one those contiguous ranges is called an Area. Areas are indexed (1, 2, 3, etc.) and the individual cells of each area's range (remember, it is contiguous) can be indexed within that range only... cell indexes cannot cross from one area to another area. Hopefully that all made sense (in re-reading it, I am not sure how clear it is)... if not, write back and I will try to explain it with different wording.

Thanks for the explanation, I think I understand it.

And I concluded there is no straight way to use index in non contiguous range, is it right?
I think I will explore the use of "Areas".


Thanks to both of you.