Swap 2 ranges
 

I have a worksheet of start times where it is sometimes necessary to swap a
rage of 4 cells with another...say A4:D4 with A9:D9.
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 A4:D4 to empty
cells....J4:M4 (after selecting A4) then I Select A9 which would copy A9:D9
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

Swap 2 ranges
 

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.

--
Regards,
Tom Ogilvy

"Dennis Saunders" wrote:

I have a worksheet of start times where it is sometimes necessary to swap a
rage of 4 cells with another...say A4:D4 with A9:D9.
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 A4:D4 to empty
cells....J4:M4 (after selecting A4) then I Select A9 which would copy A9:D9
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

Swap 2 ranges
 

Sub SwapSelections()
Dim aryRange1
Dim aryRange2

aryRange1 = Range("A4:D4")
aryRange2 = Range("A9:D9")

Range("A9:D9") = aryRange1
Range("A4:D4") = aryRange2

End Sub


--
HTH

Bob

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

"Dennis Saunders" wrote in message
...
I have a worksheet of start times where it is sometimes necessary to swap a
rage of 4 cells with another...say A4:D4 with A9:D9.
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 A4:D4 to empty
cells....J4:M4 (after selecting A4) then I Select A9 which would copy
A9:D9 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

Swap 2 ranges
 

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.