What's the best way to swap two ranges. I tried this.

Dim temp As Range

...

temp = Range(rangeName1)
Range(rangeName1) = Range(rangeName2)
Range(rangeName2) = temp

...

and of course it is not working

Thanks,
Tommy


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=390386

You need to use set statements and the copy function. Also you need temporary
memory to hold the range. Something like this

Sub SwapRanges()
On Error GoTo ErrorHandler
Dim rng1 As Range
Dim rng2 As Range
Dim rngTemp As Range
Dim wksTemp As Worksheet

Set wksTemp = Worksheets.Add
Set rngTemp = wksTemp.Range("A1")

Set rng1 = Sheet1.Range("A1:A10")
Set rng2 = Sheet1.Range("B1:B10")
rng1.Copy rngTemp
rng2.Copy rng1
wksTemp.UsedRange.Copy rng2
Application.DisplayAlerts = False
wksTemp.Delete
ErrorHandler:
Application.DisplayAlerts = True
End Sub
--
HTH...

Jim Thomlinson


"TommySzalapski" wrote:


What's the best way to swap two ranges. I tried this.

Dim temp As Range

...

temp = Range(rangeName1)
Range(rangeName1) = Range(rangeName2)
Range(rangeName2) = temp

...

and of course it is not working

Thanks,
Tommy


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=390386

Hi Tommy,

Try something like:

'=============================
Sub TestIt()
SwapRanges Range("One"), Range("Two")

End Sub


Sub SwapRanges(Rng1 As Range, Rng2 As Range)
Dim Arr As Variant

Arr = Rng1

Rng1 = Rng2.Value
Rng2 = Arr

End Sub
'<<=============================
---
Regards,
Norman



"TommySzalapski"
wrote in
message news:TommySzalapski.1ssrqk_1122415560.206@excelfor um-nospam.com...

What's the best way to swap two ranges. I tried this.

Dim temp As Range

..

temp = Range(rangeName1)
Range(rangeName1) = Range(rangeName2)
Range(rangeName2) = temp

..

and of course it is not working

Thanks,
Tommy


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile:
http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=390386

Very nice... Use this code (if you do not need to copy the formats). It is a
pile more efficient than mine. I was assuming formats had to go with the
range so I never even thought to use a variant...
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

Hi Tommy,

Try something like:

'=============================
Sub TestIt()
SwapRanges Range("One"), Range("Two")

End Sub


Sub SwapRanges(Rng1 As Range, Rng2 As Range)
Dim Arr As Variant

Arr = Rng1

Rng1 = Rng2.Value
Rng2 = Arr

End Sub
'<<=============================
---
Regards,
Norman



"TommySzalapski"
wrote in
message news:TommySzalapski.1ssrqk_1122415560.206@excelfor um-nospam.com...

What's the best way to swap two ranges. I tried this.

Dim temp As Range

..

temp = Range(rangeName1)
Range(rangeName1) = Range(rangeName2)
Range(rangeName2) = temp

..

and of course it is not working

Thanks,
Tommy


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile:
http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=390386

Thanks guys, yeah I knew I could do it using a new worksheet, but that'
clumsy. I'll use Norman's to swap values (all I had to do was chang
the word Range to the word Variant, so simple, thanks

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...fo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=39038