Macro for swapping contents of two cells in column B, contents in column A should remain unchanged, other colums in spreadsheet are empty

[HarrySiers]

Hello Forumers,

Is it possible to swap the contents of cells B1 and B2 would the macro for it be adjustable in such a way that one can swap the contents of pair of two cells in column B by changing variables in the macro? (For instance: swap B6 and B20, after that swap B4 and B13, whatever. Note: the cells in column A contain text that should remain unchanged, all other columns in the spreadsheet are empty.

Harry

 

[onlyadrafter]

Hello,

this can only be achieved if there is a set rule for the two cells to be swapped, whether it be programmed into the macro, or if the cells refs are in the spreadhsheet.

 

[HarrySiers]

Thank you, onlyadrafter,

If I understand it well, it is not possible to make a macro to swap the contents of a cell with the contents of a cell immediately below it, and a macro to swap the contents of a cell with the contents of a cell two rows below it, and a macro to swap the contents of a cell with the contents of a cell three rows below it, etc. To assign this to for instance Alt/1, Alt/2, Alt/3, etc.? - It would be great if I could use hotkeys like this to swap, the numbers in Alt/[n] referring to the row distance between the two cells and the hotkeys 'starting to count' from the position the cursor is in. In cell B6 for instance Alt/3 would then swap the contents of B6 and B9. Maybe I should not have used the word macro here but hotkey?

 

[Rick Rothstein]

Here is a macro that will swap the value in any two cells you select, whether in the same column or not. The macro will only work if exactly two cells are selected, otherwise it will do nothing. Just select the first cell, then press/hold the CTRL key down and select the second cell... then run this macro,

Sub SwapSelectedCells()
  Dim Temp As Variant, Parts As Variant
  If Selection.Count = 2 Then
    Parts = Split(Replace(Selection.Address, ":", ","), ",")
    Temp = Range(Parts(0)).Value
    Range(Parts(0)).Value = Range(Parts(1)).Value
    Range(Parts(1)).Value = Temp
  End If
End Sub

 

[HarrySiers]

Thank you very much, Mr Rothstein! That works fine. Can the macro be assigned to a hotkey, such as ALT/S for 'swap'? I am a layman in Excel, I think I read somewhere one can assign a macro to a key combination with Setkey or Run Setkey or something like that, but I wouldn't know how to do so. Anyway, thanks again!

 

[Rick Rothstein]

Thank you very much, Mr Rothstein! That works fine. Can the macro be assigned to a hotkey, such as ALT/S for 'swap'? I am a layman in Excel, I think I read somewhere one can assign a macro to a key combination with Setkey or Run Setkey or something like that, but I wouldn't know how to do so. Anyway, thanks again!

After you have placed the macro's code in a general Module, go back to any worksheet and press ALT+F8, select the macro name from the list and click the Options button... you can give it a CTRL+(a letter) shortcut there.