swap values in vba

[lezawang]

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.

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

 

[CSmith]

Well what would you like to happen if the ranges differ in size?

 

[Norie]

You are already using an array and you could easily expand on the posted code, if the ranges to swap are the same size.

Sub myswap()
Dim x As Variant

    x = Range("A1:A10").Value
    Range("A1:A10").Value = Range("B1:B10").Value
    Range("B1:B10").Value = x
    
End Sub

 

[lezawang]

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

 

[lezawang]

You are already using an array and you could easily expand on the posted code, if the ranges to swap are the same size.

Sub myswap()
Dim x As Variant

    x = Range("A1:A10").Value
    Range("A1:A10").Value = Range("B1:B10").Value
    Range("B1:B10").Value = x
   
End Sub

Thank you. Both column A and B ended empty cells from 1:10. Thank you.

 

[CSmith]

Something like this?

Public Sub mySwap(source, target As Range)
  Dim cell, temp, orgSht As Variant
  Application.EnableEvents = False
  Set orgSht = ActiveSheet
  ActiveWorkbook.Sheets.Add.Name = "swapTemp"
  Set temp = ActiveWorkbook.Sheets("swapTemp").Range(source.Address)
  temp.Value = orgSht.Range(source.Address).Value
  For Each cell In target.Cells
    source.Cells(cell.Row - target.Offset(0, 0).Row + 1, cell.Column - target.Offset(0, 0).Column + 1).Value = cell.Value
  Next
  For Each cell In temp.Cells
    target.Cells(cell.Row - temp.Offset(0, 0).Row + 1, cell.Column - temp.Offset(0, 0).Column + 1).Value = cell.Value
  Next
  Set cell = Nothing
  Set temp = Nothing
  Application.DisplayAlerts = False
  ActiveWorkbook.Sheets("swapTemp").Delete
  Application.DisplayAlerts = True
  Application.EnableEvents = True
  orgSht.Activate
  Set orgSht = Nothing
End Sub

 

[footoo]

What about:

[A:A].Cut
[C:C].Insert

 

[Akuini]

Maybe this:

Sub try288()
Dim n As Long
Dim va

n = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
va = Range("A1:A" & n)
Range("A1:A" & n).Value = Range("B1:B" & n).Value
Range("B1:B" & n) = va

End Sub

 

[CSmith]

Pretty sure the rest of these examples leave cells with #N/A when cell ranges differ.

 

[footoo]

Pretty sure the rest of these examples leave cells with #N/A when cell ranges differ.

What about post #7 ?