Swapping the contents of 2 ranges...

[RobbieC]

Hi there, I have 2 ranges - A1:A10 & B1:B10. I'm tring to work out how I can get the contents of 2 ranges to swap (regardless of whether they are blank or not) using a macro on a button

Any pointers would be really helpful.

Thanks

 

[RobbieC]

Would it make things easier if the ranges were named? ie A1:J1 named RowRange1 & A2:J2 named RowRange2

 

[RobbieC]

Would naming the ranges be of any use? ie A1:J1 names RowRange1 and A2:J2 named RowRange2...

I'm clutching at straws now... (apologies for duplicate post - I didn't realise that it had gone over to page 2)

 

[Rick Rothstein]

Would it make things easier if the ranges were named? ie A1:J1 named RowRange1 & A2:J2 named RowRange2

Not necessary... give this code a try.

Sub SwapRanges()
  Dim Range1 As Variant, Range2 As Variant
  Range1 = Range("A1:J1")
  Range2 = Range("A2:J2")
  Range("A1:J1") = Range2
  Range("A2:J2") = Range1
End Sub

 

[RobbieC]

Rick, you are a genius! Works like a trooper!

Thanks very much mate!

 

[Rick Rothstein]

Rick, you are a genius!

You are too kind... and wrong, but thanks for the sentiment.

Works like a trooper!

Thanks very much mate!

You are quite welcome, but it just occurred to me that we can use the standard Temp variable approach and eliminate one variable declaration and one line of code as well...

Sub SwapRanges()
  Dim Temp As Variant
  Temp = Range("A1:J1")
  Range("A1:J1") = Range("A2:J2").Value
  Range("A2:J2") = Temp
End Sub