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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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).  

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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.

Glenn RayExcel 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.