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

 

[mole999]

KUTOOLS has that as a command bar button if you are doing it infrequently

 

[RobbieC]

I can get it to work for 2 cells using

[COLOR=#339CCB][FONT=Courier New]Dim[/FONT][/COLOR][COLOR=#666666][FONT=Courier New] temp [/FONT][/COLOR][COLOR=#339CCB][FONT=Courier New]As[/FONT][/COLOR][COLOR=#666666][FONT=Courier New] [/FONT][/COLOR][COLOR=#339CCB][FONT=Courier New]Double
[/FONT][/COLOR][COLOR=#666666][FONT=Courier New]temp = Range("A1").Value
[/FONT][/COLOR][COLOR=#666666][FONT=Courier New]Range("A1").Value = Range("B1").Value
[/FONT][/COLOR][COLOR=#666666][FONT=Courier New]Range("B1").Value = temp[/FONT][/COLOR]

but I can't get it to work for 10 cells in a range...

 

[mole999]

Dim n
Dim temp AsDouble
For N = 1 to 10 step 1
temp = Range("A" & n).Value
Range("A" & n).Value = Range("B" & n).Value
Range("B" & n).Value = temp
next n

 

[RobbieC]

ADVERTISEMENT

I just realised I got my question wrong.... my rage is the row not the column...

So it's A1:J1 and A2:J2

Sorry mate - schoolboy error

 

[mole999]

I'm rather weak on columns, I know they can be done, but I don't have anything useful to suggest as coding it once would be easy, having it change each time would be more difficult for me

 

[RobbieC]

ADVERTISEMENT

Also, my ranges contain text, not just numbers... The above code works for columns fine, but only if they contain numbers and not text

 

[mole999]

try it without .value

 

[jimrward]

Do you have any data beyond column J

 

[RobbieC]

Yeah, it's a whole sheet full of data. I'm looking to provide the function to swap the row ranges A1:J1 and B2:J2 (which contain text as well as numbers) and if I can get this to work, I will look to use it in other places on the same sheet

K1:K100 all the way up to Z1:Z100 is full of other stuff...