Is there a way to switch the content of two cells

[Bossmanjames]

If i want to move content from cell A to cell B, i currently have to cut and paste cell B's content somewhere else, THEN move cell A's content to cell B. Is there a way to simply highlight both cells and simply switch them automatically on the fly.

 

[63falcondude]

How To Swap Values Between Cells, Rows, Or Columns In MS Excel

 

[Joe4]

Here is a VBA option.

Just highlight the two cells you want to swap, and run it. You could even assign the macro to a keyboard shortcut to make it easier/quicker to run.

Sub SwitchCells()

    Dim cell As Range
    Dim firstVal
    Dim secondVal

'   Make sure that they have selected exactly two cells
    If Selection.Count <> 2 Then
        MsgBox "You MUST select exactly two cells to switch before running!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Capture values
    firstVal = Selection(1).Value
    secondVal = Selection(2).Value
    
'   Assign values
    Selection(1).Value = secondVal
    Selection(2).Value = firstVal
        
End Sub

 

[Bossmanjames]

How To Swap Values Between Cells, Rows, Or Columns In MS Excel

when i try this i just a message saying "this operation is not allowed..."

Here is a VBA option.

Just highlight the two cells you want to swap, and run it. You could even assign the macro to a keyboard shortcut to make it easier/quicker to run.

Sub SwitchCells()

    Dim cell As Range
    Dim firstVal
    Dim secondVal

'   Make sure that they have selected exactly two cells
    If Selection.Count <> 2 Then
        MsgBox "You MUST select exactly two cells to switch before running!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Capture values
    firstVal = Selection(1).Value
    secondVal = Selection(2).Value
    
'   Assign values
    Selection(1).Value = secondVal
    Selection(2).Value = firstVal
        
End Sub

What is VBA and how do i use it, and how do i assign it to a keyboard shortcut

 

[Joe4]

What is <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> and how do i use it, and how do i assign it to a keyboard shortcut

See here: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

To assign a keyboard shortcut, see:
Assigning Macros to Short-Cut Keys in Excel

 

[Bossmanjames]

See here: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

To assign a keyboard shortcut, see:
Assigning Macros to Short-Cut Keys in Excel

welp that didnt work. It only swaps two cells that are directly adjacent to eachother. i want to swap cells that are miles apart. I also want to swap entire rows. Not just single cells.

 

[Joe4]

welp that didnt work. It only swaps two cells that are directly adjacent to eachother. i want to swap cells that are miles apart. I also want to swap entire rows. Not just single cells.

It does work. It gave you what you asked for (but not maybe actually what you wanted).
That is why it is important to be accurate and detailed in explaining what it is exactly that you are after.

You originally said:

If i want to move content from cell A to cell B, i currently have to cut and paste cell B's content somewhere else, THEN move cell A's content to cell B. Is there a way to simply highlight both cells and simply switch them automatically on the fly.

What you are interested in will almost surely require VBA. But if you are dealing with cells that are not adjacent, or whole rows, I am not sure how you would do that with selecting them first and then running the code. I would probably write code that prompts them to enter the cells or row numbers to be moved.

 

[Joe4]

Here is VBA code that would do what you want, using the prompts I talked about.

Sub RangeSwap()

    Dim inputRangeType As String
    Dim inputRange1 As String
    Dim inputRange2 As String
    Dim input1
    Dim input2
    Dim inputRow1 As Long
    Dim inputRow2 As Long
    
'   Ask them what they want to move
    inputRangeType = InputBox("Enter C for cell or R for row", "ENTER WHAT TO SWAP")
    
'   Decide what to do
    Select Case UCase(inputRangeType)
'       Cell swap steps
        Case "C"
'           Prompt for cell address entries
            On Error GoTo cell_error
            inputRange1 = InputBox("Enter first cell address", "FIRST CELL")
            inputRange2 = InputBox("Enter second cell address", "SECOND CELL")
'           Capture values
            input1 = Range(inputRange1)
            input2 = Range(inputRange2)
'           Switch values
            Range(inputRange1) = input2
            Range(inputRange2) = input1
            On Error GoTo 0
            MsgBox "Cell swap completed!"
'       Row swap steps
        Case "R"
'           Prompt for row entries
            On Error GoTo row_error
            inputRow1 = InputBox("Enter first row number", "FIRST ROW")
            inputRow2 = InputBox("Enter second row number", "SECOND ROW")
            If inputRow1 < 1 Or inputRow1 > Rows.Count Then GoTo row_error
            If inputRow1 < 2 Or inputRow2 > Rows.Count Then GoTo row_error
'           Copy rows
            If inputRow2 > inputRow1 Then
                Rows(inputRow2 + 1).Insert Shift:=xlDown
                Rows(inputRow1).Copy Destination:=Rows(inputRow2 + 1)
                Rows(inputRow2).Copy Destination:=Rows(inputRow1)
                Rows(inputRow2).Delete
            Else
                Rows(inputRow1 + 1).Insert Shift:=xlDown
                Rows(inputRow2).Copy Destination:=Rows(inputRow1 + 1)
                Rows(inputRow1).Copy Destination:=Rows(inputRow2)
                Rows(inputRow1).Delete
            End If
            On Error GoTo 0
            MsgBox "Row swap complete"
        Case Else
            MsgBox "That is not a valid entry", vbOKOnly, "ERROR!"
    End Select
    
    Exit Sub
    
    
cell_error:
    MsgBox "You have entered an invalid cell address!"
    Exit Sub
    
row_error:
    MsgBox "You have entered an invalid row number!"
    Exit Sub
                
End Sub

If you copy it into VBA and run it, it is pretty self-explanatory/self-guiding.