Cell Transpore

[mole999]

A Thought from another thread, anyone have code that would do this (more elegantly)

Sub Transpose()
Application.OnKey "+^{ALTGR}"
Dim VarA As String
Dim VarB As String
    VarA = Range("A1")
    VarB = Range("B1")
    Range("A1") = VarB
    Range("B1") = VarA
End Sub

two cells selected, on a short cut key (something appropriate), swap the data via code

seems simple though I don't recall seeing anything like this on the forum

 

[vds1]

One less variable

Sub Transpose()
Application.OnKey "+^{ALTGR}"
Dim Swap As String
    Swap = Range("A1")
    Range("A1") = Range("B1")
    Range("B1") = Swap
End Sub

 

[mole999]

OK, but I was sort of looking for a way that by selecting two cells (at random) and pressing a short cut, the two would swap, I could never guarantee that i would only ever use just two cells

 

[Rick Rothstein]

A Thought from another thread, anyone have code that would do this (more elegantly)

Sub Transpose()
Application.OnKey "+^{ALTGR}"
Dim VarA As String
Dim VarB As String
    VarA = Range("A1")
    VarB = Range("B1")
    Range("A1") = VarB
    Range("B1") = VarA
End Sub

two cells selected, on a short cut key (something appropriate), swap the data via code

seems simple though I don't recall seeing anything like this on the forum

Give this a try...

Sub SwapTwoCells()
  Dim Temp As Variant
  If Selection.Cells.Count <> 2 Then Exit Sub
  If Selection.Areas.Count = 2 Then
    Temp = Selection.Areas(1)
    Selection.Areas(1) = Selection.Areas(2)
    Selection.Areas(2) = Temp
  Else
    Temp = Selection(1)
    Selection(1) = Selection(2)
    Selection(2) = Temp
  End If
End Sub

 

[mole999]

Perfect

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sub SwapTwoCells()
  Dim Temp As Variant
  If Selection.Cells.Count <> 2 Then Exit Sub
  If Selection.Areas.Count = 2 Then
    Temp = Selection.Areas(1)
    Selection.Areas(1) = Selection.Areas(2)
    Selection.Areas(2) = Temp
  Else
    Temp = Selection(1)
    Selection(1) = Selection(2)
    Selection(2) = Temp
  End If
End Sub