Dragging and replacing cell values

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi,
I would like to drag one cell to another on the same worksheet so that the
contents from those cells are swapped. Eg. if I drag cell A2 to B3, then
A2 replaces B3, and the content of A2 becomes what was in B3. I suspect
this is not possible, so if not, is there a way (via a VB procedure) to
select 2 cells and then click a procedure button to automatically swap the
contents of the 2 selected cells.
Thank you for any assistance.
Rob
 
Rob,

You can't do that with drag and drop. Here's a macro that will
swap the contents of two cells.

Sub Swap()
Dim Temp As Variant
If Selection.Cells.Count <> 2 Then
MsgBox "You must select two cells."
Exit Sub
End If
Select Case Selection.Areas.Count
Case 1
Temp = Selection.Cells(1, 1)
Selection.Cells(1, 1) = Selection.Cells(2)
Selection.Cells(2) = Temp
Case 2
Temp = Selection.Cells(1, 1)
Selection.Cells(1, 1) = Selection.Areas(2).Cells(1, 1)
Selection.Areas(2).Cells(1, 1) = Temp
Case Else
End Select
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
If they are in adjacent cells, (right& left or top&bottom), a
Shift+Drag&Drop while not performing a swap operation per se, effectively
swaps the cell contents.

PC
 
Thanks for your input Paul. Its a limited option but the procedure from
Chip does the job very well.
Rob
 
Back
Top