Excel - Moving Rows of Data

[phlyx]

We have some pretty massive spreadsheets and in some of our currently required updates we have to move rows of data from one place in the spreadsheet to another. For example the data on row 10 might need to be moved to row 950. So we have to highlight row 10, cut, then scroll all the way down to row 950 and then paste.

Is there any way to do this quicker? Or at least any way to be (for example) on row 10 and JUMP to row 950??? The best would be some way to just be able to MOVE the data without having to cut and paste.

Any help??? Thanks in advance! :O)

~ Phlyx ~

 

[SkipVought]

phlyx,

Sorting is usually a MUCH BETTER method than cut 'n' paste, particularly whenb you have formulas that depend on another row's value.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fkegley]

I agree with SkipVought that sorting is a much better alternative. However, you could try developing a macro to do the following:

You would need to practice this, of course, but it would be something like: the user types in the row number of the cells to be moved and the row number of the row that will be below the new row. Once that has been done, the macro would then select the cells to be cut, cut them, select the cells of the row that will be below the new row, insert a row, select the leftmost cell of the new row, and then paste.

Frank kegley
fkegley@hotmail.com

 

[phlyx]

When you say "sorting" do you mean have an "order" identifier column and then just edit this number and re-sort the data????

~ Phlyx ~

 

[SkipVought]

Does your data not have significant column(s) for sorting?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[phlyx]

Actually I was looking into this for a friend and she finally described the specifics of the spreadsheet and it turns out she is using Excel as a glorified typewriter. I suggested either making a column as a sort field OR making each sub-category have a field variable and if you wanted to more something from one sub-category to another then just change the "key" field and re-sort it. Oh well... thanks for the suggestions.

By the by... any way to JUMP to a specific row/cell?

Thanks!

~ Phlyx ~

 

[SkipVought]

Edit/Go To...

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jfhewitt]

Define row to be moved, write macro as:
Sub MoveRow()

Selection.Cut
rownum = InputBox("Enter Row Destination..R##C1")
Application.Goto Reference:=rownum
ActiveSheet.Paste

End Sub

If moving entire row, be sure destination is column 1. To make it easy, run macro by a toolbar button.

 

[phlyx]

Thanks for the macro but when I highlight a row (or a cell), run the macro, enter a number (or anything) I get the error message :

Run-time error '1004':
Reference is not valid

and the error debugger goes to the line :

Application.Goto Reference:=rownum

Sorry for not being more VB literate....

~ Phlyx ~

 

[SkipVought]

    rownum = InputBox("Enter Row Destination..R##C1")
    Selection.Cut Destination:=Cells(rownum, 1)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[phlyx]

THANKS!!!!!!!!!!!!!!!

:O)

~ Phlyx ~

 

[phlyx]

What I ended up with is as follows :

Sub MoveRow()

Selection.Cut
rownum = InputBox("Enter Row Destination..R##C1")
Selection.Cut Destination:=Cells(rownum, 1)

End Sub

This works fine to CUT the cell(s)/row(s) and can work just fine on multiple rows of data. Really helped my friend out of a jam having to throw around lines of data on a 1,000+ row spreadsheet.

Many thanks (again)!

~ Phlyx ~

 

[SkipVought]

You only need the SECOND Selection.Cut

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884