Swapping the contents of 2 ranges...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
376
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
KUTOOLS has that as a command bar button if you are doing it infrequently
 
Upvote 0
I can get it to work for 2 cells using
Code:
[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...
 
Upvote 0
Code:
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
 
Upvote 0
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 :(
 
Upvote 0
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
 
Upvote 0
Also, my ranges contain text, not just numbers... The above code works for columns fine, but only if they contain numbers and not text
 
Upvote 0
Do you have any data beyond column J
 
Upvote 0
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...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top