Moving rows up and down a table with VBA

[xltrader100]

I have a table that's 8 cols wide and any number of rows. One of the things that will happen frequently is that the user will select some number of contiguous rows and drag them to a new position in the table, and I'm trying to make that easier for them because a long drag can be kind of perilous.

If the user was doing it manually, he'd insert the same number of blank rows as he's going to drag, then do the drag and drop, and then repair the gap that was left. I have no problem in simulating those actions in VBA but where I'm stuck is in detecting what the user's intentions are.

Ideally, I'd like to have him select his block of rows, then go and click on another cell up or down the table to indicate where they should go, and then click a control that says "put 'em there". Of course as soon as he clicks on another cell the original selection goes away. I can't use a right-click because that's used for something else. If he Ctrl-clicks then the clicked cell gets selected and the original selection stays shaded but I don't know how to reference the shaded cells after the click.

Another way that looks promising is to trap all mouse-down events on any table cell (which happens before the click event) and then set a reference to any selected rows before they get deselected by the click event. And then await developments. If his intention turns out to be moving the rows, then I'm good to go, or if the eventual click led to some other action then so be it. Looking for ideas.

[Bob Phillips]

The easiset way is to get them to do it via a macro, first ask for the range to be moved, then ask for the start cell to move to (all using Application.Inputbox(..., Type:=8)), and then just manipulate the ranges.

[xltrader100]

Thanks, xld but that's too many steps. I'm looking for simple simple from the user's pov. i.e. no typing.

[lucas]

It won't get any simpler than that. Bob's method opens an input box......then, from there they select the rows to move and then select the cell in column A to move them to. How could it be simpler than that?

[Bob Phillips]

Exactly as Steve says. Click one button, select a target range, select a destination cell. Three steps, but three easy steps, as against the (sometimes) tricky drag-and-drop.

There is no event you can piggy-back on, so you have to control it.

[xltrader100]

I've already eliminated d&d as an option. That's the whole point of the exercise. And this is a graphically oriented UI where things get done by selecting and clicking, not by typing.

[Bob Phillips]

my solution is clicking
Click - the macro button
Select-click (with the mouse) - the target range to be cut
Click - the destination cell

not a type in sight.

[xltrader100]

Ok granted, no typing. But I guess I just have an aversion to those ugly Input boxes, which you have to read first to figure out what to do next. It breaks up the flow.

Here's what I came up with, and it seems to be working alright so far so I'm going to mark this thread Solved.

In a Worksheet_SelectionChange event macro I put
Application.GoTo Selection

which doesn't change anything on the sheet because it's already at the selection, but the GoTo adds an entry to the Previous Selections array, which my code can access if the user selects another cell and then clicks on my Move_Rows Cmd button.

[mdmackillop]

Try this

[xltrader100]

Pretty slick, md. In fact it's amazing. I am totally in awe that you did that in so few lines of code when I compare it to the kludge I was generating here. Wow. Well Done.