How to swap rows - rapidly?

[count]

Hi,
How to swap adjacent rows?
Needs to work more efficiently than looping through cells.
Purpose is to give user SpinUp/SpinDown to manipulate dropdown lists
(multicol)
So nifty solutions for management of namedRanges are welcome too!
TIA
Paul

 

[Vasant Nanavati]

To move up:

Selection.EntireRow.Cut
Selection.Offset(-1).Insert xlShiftDown
Selection.Offset(-1).Select

To move down:

Selection.EntireRow.Cut
Selection.Offset(2).Insert xlShiftDown
Selection.Offset(1).Select

You will need error-checking for the range extremities.

 

[Bob Flanagan]

It sounds like what you need to do is to store the entries for your combo
box in an array and set the combox list to the array versus what I suspect
you are doing, which is using the row source property to set the list.
Array manipulations are far faster and setting a list by an array should be
faster than use of row source.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel