Swap range values

[TommySzalapski]

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

[Jim Thomlinson]

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

[Norman Jones]

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jim Thomlinson]

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"
> <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[TommySzalapski]

Thanks guys, yeah I knew I could do it using a new worksheet, but that's clumsy. I'll use Norman's to swap values (all I had to do was change the word Range to the word Variant, so simple, thanks) and if I ever need to swap formats and everything, I'll make a new sheet.