switch cells

[icegg]

This might be a stupid question,but,
how to switch cells?
Say, to switch A2:D2 with A6:D6. What I know is cut A2:D2 to some empty cells outside the table, say A70, and move A6:C6 to A2 and move A50 to A6.
Is there a much easier way to do this? Does Excel has such a tool?

Thanks.

[Bob Phillips]

You can select and drag them over.

[icegg]

Tried, and it replaced the contents of destination cells. :(

[Bob Phillips]

If they were empty, how is that a problem?

[icegg]

I got your point. :)
But instead of using a third group of cells, can I switch them directly using some kind of command or macro?

[Bob Phillips]

Dim rng1 As Range
Dim rng2 As Range
Dim ary1, ary2

Set rng1 = Range("E15:F16")
Set rng2 = Range("I18:J19")
ary1 = rng1
ary2 = rng2
rng1 = ary2
rng2 = ary1

[lucas]

voila....it can be done....

[Bob Phillips]

there ought to be some error checking though, in case the ranges are different sizes.

[mdmackillop]

You could use Resize to ensure the ranges match
Sub Switch()

Dim rng1 As Range
Dim rng2 As Range
Dim ary1, ary2
Dim Rws As Long, Cols As Long

Cols = 2
Rws = 1

Set rng1 = Range("E15").Resize(Rws, Cols)
Set rng2 = Range("I18").Resize(Rws, Cols)
ary1 = rng1
ary2 = rng2
rng1 = ary2
rng2 = ary1

End Sub

[Bob Phillips]

Doesn't sound a good idea to me, that just stomps all over it. Dropping out if the nominated ranges are different shapes and sizes is a better approach IMO.

[mdmackillop]

As well be hung for a sheep as a lamb!

Sub Switch2()

Dim R1 As String, R2 As String, Cols As Long
Dim rng1 As Range
Dim rng2 As Range
Dim ary1, ary2
Dim Rws As Long
Dim Data As String

Data = InputBox("Enter first cell of both ranges, followed by number of columns" & vbCr _
& "e.g. A1,C5,2")
R1 = Trim(Split(Data, ",")(0))
R2 = Trim(Split(Data, ",")(1))
Cols = CLng(Split(Data, ",")(2))

Rws = 1

Set rng1 = Range(R1).Resize(Rws, Cols)
Set rng2 = Range(R2).Resize(Rws, Cols)
ary1 = rng1
ary2 = rng2
rng1 = ary2
rng2 = ary1

End Sub