Swap 2 ranges

[Dennis Saunders]

I have a worksheet of start times where it is sometimes necessary to swap a
rage of 4 cells with another...say A44 with A99.
I've found a macro which swaps only 2 cells.... say A25 with A3 but I don't
know if it could be modified.
All I can think of is a macro which would say copy A44 to empty
cells....J4:M4 (after selecting A4) then I Select A9 which would copy A99
to J9:M9. After this the macro would go J64000 endX1 up cut J9:M9.....A1 end
down , offset (1,0) Paste ....same for J4:M4. Looks inelegant and I'd have
to work out how to select a cell in the middle of a macro.
Any ideas if this macro could be modified?

Sub SwapSelections()
Dim rCell1 As Range
Dim rCell2 As Range
Dim strg1 As String, strg2 As String

If Selection.Cells.Count > 2 Or Selection.Cells.Count < 2 Then
MsgBox "Your selection should only contain 2 cells", vbCritical
End
End If

If Selection.Areas.Count > 1 Then
Set rCell1 = Selection.Areas(1).Cells(1, 1)
Set rCell2 = Selection.Areas(2).Cells(1, 1)

ElseIf Selection.Rows.Count > Selection.Columns.Count Then
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("A2")
Else
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("B1")
End If


strg1 = rCell1
strg2 = rCell2
rCell1 = strg2
rCell2 = strg1

End Sub


Regards Dennis

 

[Tom Ogilvy]

As long as they won't be adjacent ( or as long as you select them as separate
areas they can be adjacent) and the areas are identical in shape and size

Sub ABC()
Dim v As Variant, v1 as Variant
With Selection
v = .Areas(2).Value
v1 = .Areas(1).Value
.Areas(2) = v1
.Areas(1) = v
End With

End Sub
That should give you the idea. This doesn't copy formatting however.

 

[Bob Phillips]

Sub SwapSelections()
Dim aryRange1
Dim aryRange2

aryRange1 = Range("A44")
aryRange2 = Range("A99")

Range("A99") = aryRange1
Range("A44") = aryRange2

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dennis Saunders]

Brilliant Tom (thanks Bob).
And so easy...my macro (just written) works but definitely not elegant with
lots of "select" and relative references.
Best regards , Dennis.