Move Selection of Cells Up & Down in Microsoft Excel

Is it possible to make a macro that moves a group of selected cells up or down in MS Excel and have the cells above the selection move down one cell or above a cell depending on the direction you move it? I have spent a long time trying to figure out a way to do this and am stuck.

Here are two examples of what I would like to do as it is done in OmniFocus and Scrivener. The key command for both is split finger (Control+Command) and up and down arrow keys.

http://reference.StudioPrime.com/forums/macscripter/MS_Excel/OmniFocus_Group_Move_for_Excel.gif

http://reference.StudioPrime.com/forums/macscripter/MS_Excel/Scrivener_-_Excel_Wish.gif

The trick with MS Excel is I need to select two columns and more than one row and retain formatting of the cells.
These pictures and Excel document should add any further explaination to what I am after with an AppleScript.

http://reference.StudioPrime.com/forums/macscripter/MS_Excel/Applescript_Excel_To_Move_Selected_Cells_Up_And_Down_One_Row.zip

Here is a AppleScript ccstone wrote that does one row at a time with multiple columns.


--http://forum.keyboardmaestro.com/t/clipboard-formatting-ms-excel/1400/7
--Move one selected row but multiple columns up one row.

tell application "Microsoft Excel"
	tell active window
		
		set fontColorList to {}
		
		tell selection
			set valueList to its value
			set rowList to its rows
		end tell
		
		repeat with i in rowList
			set end of fontColorList to color of font object of i
		end repeat
		
		set value of selection to reverse of valueList
		
		set fontColorList to reverse of fontColorList
		
		set n to 0
		
		repeat with i in rowList
			set n to n + 1
			set color of (font object of i) to (item n of fontColorList)
		end repeat
		
	end tell
end tell

--http://forum.keyboardmaestro.com/t/clipboard-formatting-ms-excel/1400/7
--Move one selected row but multiple columns down one row

tell application "Microsoft Excel"
	tell active window
		
		set fontColorList to {}
		
		tell selection
			set valueList to its value
			set rowList to its rows
		end tell
		
		repeat with i in rowList
			set end of fontColorList to color of font object of i
		end repeat
		
		set value of selection to reverse of valueList
		
		set fontColorList to reverse of fontColorList
		
		set n to 0
		
		repeat with i in rowList
			set n to n + 1
			set color of (font object of i) to (item n of fontColorList)
		end repeat
		
	end tell
end tell

Hi there,

Give these a try.
Select the required range before executing.


-- Move selection up
tell application "Microsoft Excel"
	
	set thisSelection to selection
	set rowCount to count of rows of thisSelection
	
	set firstRowVals to string value of first row of thisSelection
	set firstRowValsTextCol to color of font object of first row of thisSelection
	
	repeat with i from 1 to (rowCount - 1)
		
		set moveVals to string value of row (i + 1) of thisSelection
		set moveValsColour to color of font object of row (i + 1) of thisSelection
		
		set value of row i of thisSelection to moveVals
		set color of font object of row i of thisSelection to moveValsColour
		
	end repeat
	
	set value of row rowCount of thisSelection to firstRowVals
	set color of font object of row rowCount of thisSelection to firstRowValsTextCol
	
end tell

and …


-- Move selection down
tell application "Microsoft Excel"
	
	set thisSelection to selection
	set rowCount to count of rows of thisSelection
	
	set lastRowVals to string value of last row of thisSelection
	set lastRowValsTextCol to color of font object of last row of thisSelection
	
	repeat with i from rowCount to 2 by -1
		
		set moveVals to string value of row (i - 1) of thisSelection
		set moveValsColour to color of font object of row (i - 1) of thisSelection
		
		set value of row i of thisSelection to moveVals
		set color of font object of row i of thisSelection to moveValsColour
		
	end repeat
	
	set value of row 1 of thisSelection to lastRowVals
	set color of font object of row 1 of thisSelection to lastRowValsTextCol
	
end tell

HTH

Thanks TecNik for your work on this, move down works with one execute for me and does what I was looking for but only lets me run it one time on the selection and then I have to reselect the cels again that were moved since it expands my selection. Move up doesn’t move them up for me at all in Excel 2011 and moves the top selection all the way to the bottom and moves the bottom selection up instead of moving them up or down.

The move down AppleScript looks like the building block to go from, if I can just get the selection to move down with the original selection I would be all set. I’ll see what I can figure out.

Thank you very much for your time on this.

Well shoot I can’t figure this out the AppleScripts will move the highest row selected to the bottom of the selection and vice versa but won’t move the selected rows up or down while retaining the selection. If there were some way to get selection and then just move the top and bottom rows down one when moving down and up when moving up.

Here is an animated gif of what these two the two AppleScripts do which is different then what I am after. Each AppleScript is run four times on a selection of four to get back to where it started.
http://reference.StudioPrime.com/forums/macscripter/MS_Excel/MS_Excel-Move_Down.gif
http://reference.StudioPrime.com/forums/macscripter/MS_Excel/MS_Excel-Move_Up.gif

This is more like what I am after
http://reference.studioprime.com/forums/macscripter/MS_Excel/OmniFocus_Group_Move_for_Excel.gif
http://reference.studioprime.com/forums/macscripter/MS_Excel/Scrivener_-_Excel_Wish.gif

As you can see from both of these that the selection is retained and moves up and down respectively with the moved cells. This would need to be done for at least two columns and multiple rows in my case.

Anyone have any idea how to accomplish this?

For what it’s worth if someone comes across this later here is a link of how to do this with VBA. It doesn’t run super fast but it works!


Sub MoveSelectionUp()
  Dim Rng As Range, UnusedRow As Long
  UnusedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Row + 1
  If Selection.Row > 1 Then
    Selection(1).Offset(-1).Resize(, Selection.Columns.Count).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(-1)
    Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Copy Selection.Offset(Selection.Rows.Count - 1)(1)
    Selection.Offset(-1).Resize(, Selection.Columns.Count).Select
    Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Clear
  End If
End Sub

Sub MoveSelectionDown()
  Dim Rng As Range, UnusedRow As Long
  UnusedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Row + 1
  If Selection.Row < Rows.Count Then
    Selection(1).Offset(Selection.Rows.Count).Resize(, Selection.Columns.Count).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(1).Resize(, Selection.Columns.Count)
    Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Copy Selection(1)
    Selection.Offset(1).Resize(, Selection.Columns.Count).Select
    Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Clear
  End If
End Sub

https://www.mrexcel.com/forum/excel-questions/1050673-move-selection-up-down-around-other-selections.html#post5045245

I’d MUCH rather this work as AppleScript so I didn’t have to have spreadsheets with saved macros in them and it could work across any sheet I am without workbooks open that have these macros but at least it works.