swap values in vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I wrote the code below but I want to improve it using array if possible. I want my code not only swap one cell with another cell but group of cells with another group. I want to make it dynamic means number of cells can be change but it is always cells in column B with cells in column A. Is that possible? Thank you very much.

Code:
Sub myswap()
    Dim x As Variant
    x = Range("a1").Value
    Range("a1").Value = Range("b1")
    Range("b1").Value = x
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Post#4:
Thanks for your reply. If B has lets say 10 cells and A has 5 cells then after the swap column A will have 10 cells and column B will have 5 cells. Thanks once again
What about:
VBA Code:
[A:A].Cut
[C:C].Insert
Yes I did. So ?
Maybe you just don't know what your code actually does?? AFAIK moving the entire column is not 5 or 10 cells, or maybe I just have poor english skills?.
 
Upvote 0
Post#4:
Maybe you just don't know what your code actually does?? AFAIK moving the entire column is not 5 or 10 cells, or maybe I just have poor english skills?.
My understanding (and Akiuni's) is that the OP wants to switch columns A & B regardless of how many cells are populated.
 
Upvote 0
Akuini did not seem to be doing the entire COLUMN only a portion of it...
VBA Code:
Range("A1:A" & n)
Is not an entire column.
 
Upvote 0
Akuini did not seem to be doing the entire COLUMN only a portion of it...
VBA Code:
Range("A1:A" & n)
Is not an entire column.
His code covers all populated cells, as does mine.
Provided that columns A & B have the same formatting, his code and mine do the same thing.

With your code, it seems the source range and target range have to be the same size.
 
Upvote 0
His code covers all populated cells, as does mine.
Provided that columns A & B have the same formatting, his code and mine do the same thing.

With your code, it seems the source range and target range have to be the same size.
Incorrect.

My code will swap ONLY the range of cells specified. And will begin at top left cell per what seemed to be the OP's request and subsequent elaboration. I did not ask about overlapping ranges only assumed they would not overlap.
Thanks for your reply. If B has lets say 10 cells and A has 5 cells then after the swap column A will have 10 cells and column B will have 5 cells. Thanks once again
 
Upvote 0
Incorrect.

My code will swap ONLY the range of cells specified. And will begin at top left cell per what seemed to be the OP's request and subsequent elaboration. I did not ask about overlapping ranges only assumed they would not overlap.
???
Anyway, let's wait to see what fits in with the OP's requirements.
It may be that I have misread the requirements or the OP only wants to add to her knowledge of VBA arrays - in which case my suggestion is inappropriate.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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