PDA

View Full Version : [SOLVED] Most efficient way to move a selected range up or down



JKwan
08-14-2013, 03:07 PM
If I have a range selected say A3:E10, what is the best way to move this range up or down. By that I mean if I wanted to shift the entire selection down one row (column is fixed) then one more row (x number of times) and vice versa to move the selection up again. Thanks.

p45cal
08-14-2013, 04:39 PM
Not sure about this being the best/most efficient way but this moves the selection down a row:
Sub blahDown()
Selection.Cut Selection.Offset(1)
Selection.Offset(1).Select
End Sub
and up a row:
Sub blahUp()
Selection.Cut Selection.Offset(-1)
Selection.Offset(-1).Select
End Sub
Execute the macro multiple times to move repeatedly. Will only work for a contiguous selection.

JKwan
08-14-2013, 05:52 PM
Thanks for the suggestion. I thought of using cut/paste, but I thought there may be a more elegant way. The reason why I have not use the cut/paste method is that I may have up to 2500 rows to move that is why I am looking for a more efficient method.

p45cal
08-14-2013, 11:09 PM
Well, I just tried it (xl2003) with 3000 rows (both entire rows and only 30 columns) of mixed data comprising formulae and constants and it takes a fraction of a second. In the past, I've tried inserting rows and it takes a lot longer.
If you know how many rows you want to move the block by, you don't have to do it one row at a time, you could pass an argument to the sub to substitute for the 1s that appear now.

JKwan
08-15-2013, 06:24 AM
Thank you, once again. You are correct, the range does move very quickly, which I thought otherwise.

snb
08-17-2013, 08:43 AM
You shouldn't 'move' ranges, but 'adding/deleting' rows instead.


Sub M_snb()
selection.resize(1).entirerow.insert
End sub


Sub M_snb()
selection.resize(1).offset(-1).entirerow.delete
End sub