Friday, November 22, 2013

Excel VBA macro to swap 2 values in cells

It is very useful sometimes to have a shortcut to swap 2 cell values. I have created a macro in VBA to do this. Just copy paste the code below in your VBA module.

This video will help you with the procedure


VBA Code
-------------
Sub Swap2Values()
    Dim Value1 As Range, Value2 As Range
    Dim TempValue1 As String, TempValue2 As String

    If Selection.Cells.Count > 2 Or Selection.Cells.Count < 2 Then
        MsgBox "Please select only 2 cells. For other options check back soon!"
        End
    End If
 
    If Selection.Areas.Count > 1 Then
        Set Value1 = Selection.Areas(1).Cells(1, 1)
        Set Value2 = Selection.Areas(2).Cells(1, 1)
    ElseIf Selection.Rows.Count > Selection.Columns.Count Then
        Set Value1 = Selection.Range("A1")
        Set Value2 = Selection.Range("A2")
    Else
        Set Value1 = Selection.Range("A1")
        Set Value2 = Selection.Range("B1")
    End If
    TempValue1 = Value1
    TempValue2 = Value2
    Value1 = TempValue2
    Value2 = TempValue1
End Sub

How to swap 2 excel ranges | swap with multiple cells selected

I had created a macro to swap 2 cells values in MS Excel. By just clicking a button a macro could interchange the content of 2 excel cells. ...