Swapping the contents of 2 ranges...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
KUTOOLS has that as a command bar button if you are doing it infrequently
 

RobbieC

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
try it without .value
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Do you have any data beyond column J
 

RobbieC

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

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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
Top