Friday, November 22, 2013

Excel VBA macro to swap 2 values in cells

It  is very useful some times 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 you VBA module.

This video will help you with the procedure


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