Is there a way to switch the content of two cells

Bossmanjames

New Member
Joined
Jan 12, 2017
Messages
5
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jan 12, 2017
Messages
5

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.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Here is VBA code that would do what you want, using the prompts I talked about.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top