Move Selection Up & Down Around Other Selections

[skillet]

I can't figure this out, I have a AppleScripts that 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 … e_Down.gif
http://reference.StudioPrime.com/forums … ove_Up.gif

This is more like what I am after
http://reference.studioprime.com/forums … _Excel.gif
http://reference.studioprime.com/forums … l_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.

I have been trying to do this for years now but thought I would have another go at it since though not preferred perhaps it is a job for VBA.
https://macscripter.net/viewtopic.php?id=44292

 

[Rick Rothstein]

Will there ever be any cell formatting (either is the selection itself or in the non-selected cells as well) and, if so, will it have to be preserved when the selection is moved?

 

[skillet]

Yes, sorry I should have mentioned that. I am sure that complicates things greatly.

 

[Rick Rothstein]

Yes, sorry I should have mentioned that. I am sure that complicates things greatly.

Here are two macros (you can assign them to Command Buttons or give them Shortcut Keystrokes to make them easier to work with. One of the macros moves the selection up and the other moves the selection down (the procedure names tell you which is which)...

[table="width: 500"]
[tr]
	[td]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).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(-1)
    Cells(UnusedRow, "A").Copy Selection.Offset(Selection.Rows.Count - 1)(1)
    Selection.Offset(-1).Select
    Cells(UnusedRow, "A").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.Count).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(1)
    Cells(UnusedRow, "A").Copy Selection(1)
    Selection.Offset(1).Select
    Cells(UnusedRow, "A").Clear
  End If
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Here are two macros (you can assign them to Command Buttons or give them Shortcut Keystrokes to make them easier to work with. One of the macros moves the selection up and the other moves the selection down (the procedure names tell you which is which)...

[table="width: 500"]
[tr]
	[td]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).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(-1)
    Cells(UnusedRow, "A").Copy Selection.Offset(Selection.Rows.Count - 1)(1)
    Selection.Offset(-1).Select
    Cells(UnusedRow, "A").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.Count).Copy Cells(UnusedRow, "A")
    Selection.Copy Selection(1).Offset(1)
    Cells(UnusedRow, "A").Copy Selection(1)
    Selection.Offset(1).Select
    Cells(UnusedRow, "A").Clear
  End If
End Sub[/td]
[/tr]
[/table]

The above code only works with selections that are one column wide... the code below works with selection containing any number of columns.

[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]

 

[skillet]

Thank you this is awesome! I was messing with the first one trying to figure out how to improve it because I noticed it copied things that weren't there when it encountered blank cells but this second one seems to be working perfectly, so awesome!!

 

[skillet]

It's strange that it doesn't show the selection after this runs in Excel 14.7.7 for Mac (which is much faster than the Excel 16.12 for Mac) but still has the selection if it runs again.

I added Selection.Offset(0, 0).Select to update the selection.

Sub MoveSelectedContentsUp()
  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
  Selection.Offset(0, 0).Select




End Sub




Sub MoveSelectedContentsDown()
  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
  Selection.Offset(0, 0).Select




End Sub

 

[Rick Rothstein]

It's strange that it doesn't show the selection after this runs in Excel 14.7.7 for Mac (which is much faster than the Excel 16.12 for Mac) but still has the selection if it runs again.

Sorry, I do not have a Mac, so I cannot test my code on one; however, I can see no reason why my code would not work on any version of Excel on a Mac... it is just three straight-forward copy/pastes followed by a selection (not including the selection that you added) and a range clear... nothing unusual or mysterious.

I added Selection.Offset(0, 0).Select to update the selection.

The reason I left the original selection selected was so the user could move it again and again in case they needed to move it two or more rows. Your addition would require them to reselect the range of cells before each move (might be problematic if the range were longer or wider than the screen). I also figured that it would not be too onerous for the user to simply click any cell to remove the selection that he/she was moving once finished moving it.

 

[skillet]

The reason I left the original selection selected was so the user could move it again and again in case they needed to move it two or more rows.

You are right and what you did is perfect, I certainly will be running it over and over again on the same selection. I think it is just a graphical bug in that version of Mac because it did move the same selection up but didn't show the cells as highlighted. It works perfectly fine in the latest version of Excel for Mac with or without the addition. It also runs much faster though all my AppleScript run much slower and things generally are not as fast in the new/latest version.

Your addition would require them to reselect the range of cells before each move (might be problematic if the range were longer or wider than the screen).

It actually on a mac at least keeps the selection and forces an update to show the selection is in the new spot with no reselection needed.

I also figured that it would not be too onerous for the user to simply click any cell to remove the selection that he/she was moving once finished moving it.

Absolutely agree with you, I am so excited to have this working I will use all the time and this puts a close on my last major need in the way I work in Excel daily. They'll always be endless other things I need done that can be sped up but this one I have wished for, for quite a while. Thanks so much!