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

6 comments:

  1. Great! But if i want to swap not only the value of the cells, but the format and the formula in thet cells? How can i doit?

    ReplyDelete
  2. Sorry to say this
    MS Excel Currency Converter Number to Text - spell number
    In this I found an error
    559854 is read as Fifty five lac fifty nine thousand eight hundred fifty four rupees
    instead of Five lac fifty nine thousand eight hundred fifty four rupees

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your feedback, it really helps me to improve this tool
      I have fixed the issue in the latest version, see my latest post

      Have look at it https://www.youtube.com/watch?v=t-PE5h6rGrQ


      Delete
  3. Yes i am totally agreed with this article and i just want say that this article is very nice and very informative article.I will make sure to be reading your blog more. You made a good point but I can't help but wonder, what about the other side? !!!!!!THANKS!!!!!!
    excel vba courses london

    ReplyDelete
  4. Pretty nice post. I just stumbled upon your weblog and wanted to say that I have really enjoyed browsing your blog posts. After all I’ll be subscribing to your feed and I hope you write again soon!
    excel courses

    ReplyDelete
  5. can this macro be done with two un-adjecent cells

    ReplyDelete

Cleaning up private docker registry

The solution to cleaning the unused docker registry layers is not straightforward, there is nothing readily available from docker. As more ...