Excel vba move row to specific row number

I have a spreadsheet with around 14,000 rows.   Some of the rows got put in to incorrect locations.

I would like to select a row then hit a maco key to enter the row to move to.

I am not sure on the code - any suggestion?

Who is Participating?
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Here you go!  I added a third method which will allow you to select the row to move by either clicking a cell or the entire row.  Again, the macro runs on the assumption that you've already placed your cursor on the destination row before running.

Rob HensonFinance AnalystCommented:
I take it the movement of say row 12 to row 48 will require a row inserted at row 48 for the data that is moving. Row 12 will then be empty and will need deleting.

Potentially slight flaw in the logic with the row numbers, example row 12 to move to row 48:

1) Insert row at row 48, creating new row 48 with old row 48 data now on row 49
2) Copy row 12 data and paste into new row 48
3) Delete row 12
4) New row 48 is now row 47, old row 48 which became row 49 is now back at row 48
5) Rows 13 to 47 will now be 12 to 46 - will this affect the next stage?

Will sorting the data on a particular field not move the data into the right rows?

Rob H
johnmadiganAuthor Commented:
I will insert a new row manually in the new location first.   Select the row I want to move - hit a macor key - inter the new location row number - the row moves to new row location.

The old row will just be blank now.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Glenn RayExcel VBA DeveloperCommented:
You could automate all of the steps you described, that is, inserting a new row manually, selecting the row to move there, moving the selected row (cut and paste).

I'm still unsure as to why you would want to leave the old row blank, rather than actually *move* the selected row to the new location (akin to "Insert Cut Cells" function).

I have attached a workbook which does both of the above methods.  The first one requires that you insert a blank row and then prompts you to enter the row number to move there, leaving the old row blank.  The second method requires that you place your cursor in the destination row and prompts you to enter the row to move there, removing the old row entirely and placing it in the new location (no blank rows).

You could add the macro code here to your existing workbook and then execute either one by pressing [Alt]+[F8] and running them from there.  You could also assign a shortcut key to one or both by selecting the "Options" button in the Macro dialog box (again, [Alt]+[F8], but use care because they all require [Ctrl]+ a letter key and you could overwrite a default combination (ex., [Ctrl]+[C] = copy).  

johnmadiganAuthor Commented:
Thanks for the sample file - when I tried the Method 1 it would prompt for where to move the row - after I put in the new row number and clicked o.k. it just deleted the row but did not move to the new location.

I selected the row by clicking the row number next to Col A - I tried selecting the cells in the row that need to move but still no luck.
Glenn RayExcel VBA DeveloperCommented:
Selecting the row with your cursor will not work using either method; one has to actually type in the row number (not the ID number in the example, which is phase-shifted by one to begin)

Also, Method 1 assumes the row to which you want to move was manually inserted by you (as you described in the post just before mine above).  I prefer Method 2 which inserts a row wherever your cursor happens to be and then prompts for the row number to move there (removing the old blank row that would normally be left over from a straight copy and paste).

I can try to give you another tool that will let you actually select the cell or row to move using the cursor.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.