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