Solved Swapping adjacent rows (moving a row above the row above it)

February 11, 2015 at 06:30:10
Specs: Windows 7
What if I wanted to swap adjacent rows without entering the row numbers I wanted to swap? I'm trying to write a macro that takes the currently selected row and moves it above the row above it – essentially swapping the rows. But instead of being prompted to enter the row numbers, I want to begin with the currently selected row (or the row with the selected cell). I also want a separate macro that will swap that row downward.

I found this below but it requires you to enter the row numbers you want to swap:

Sub RowSwapper()
rw1 = Application.InputBox("Enter Lower Row number")
rw2 = Application.InputBox("Enter Higher Row number")
Rows(rw2).Cut
Rows(rw1).Insert Shift:=xlDown
Rows(rw1).Cut
Rows(rw2).Insert Shift:=xlDown

My end goal is to create two arrow buttons that will move rows up (and down) for desired positioning.

I am a novice as I’m sure you can tell. Any help is appreciated.
Jarrod


See More: Swapping adjacent rows (moving a row above the row above it)

Report •

✔ Best Answer
February 12, 2015 at 07:23:45
There are lots of books and web sites related to learning to write VBA code. While I have never read a book (probably should have) I did spend a lot of time hanging out in forums and looking at what others have written, then using the debugging techniques to help understand what their code was doing, whether I needed that code or not. I'd read a question, see the answer(s) and then "debug" the code if I couldn't understand how it was doing what it was doing.

e.g. "Oh, that's how you build a comma separated text string from multiple values and then eliminate that pesky final comma!"

Once a specific technique is understood, the concept can usually be applied/expanded to fit other situations. The main thing to keep in mind is that VBA is nothing more than a logical, step-by-step set of instructions. Try recording a macro and see what the VBA editor produces. Granted, it will be inefficient, bloated code because the editor will simply record (just about) every manual step you take, but it will give you something to "debug" just to see how the debugging tools work. Eventually you will learn how to clean up recorded code to make it more efficient, but it's not a bad place to start.

Chip Pearson is probably my #1 on-line go-to resource.

http://www.cpearson.com/Excel/Topic...

Lots of formulas and VBA to play with.

I understand that John Walkenbach is a well respected author in the Excel world.

http://spreadsheetpage.com/

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 11, 2015 at 08:35:02
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.

Thanks!

As far as your question, you can reference the selected row (or the row containing the selected cell) directly and reference rows above or below it with some simple math:

Sub WhereAmI()
'Reference Selected Row:
     MsgBox Selection.Row
'Reference 1 Row above Selected Row
     MsgBox Selection.Row - 1
End Sub

See if you can work with that to accomplish your goal.

If you are just getting starting with Excel VBA, you might want to review this tutorial:

http://www.computing.net/howtos/sho...

Give it a try on your own (practice!) and let us know if you need any more help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 12, 2015 at 05:11:55
Yesterday after I sent this request I was thinking I should have credited the author of that string of code I posted. Went back to see who wrote it - of course it was you. Thank you.

Thanks for the advice. I will toy with your recommendation and review the tutorial. I've been looking for a jump starter - maybe this is it.

Jarrod


Report •

#3
February 12, 2015 at 05:29:05
Derby,
I noticed your tutorial covered debugging. I'd like to be able to bug something up before I need to debug it. Do you know of any good beginner resources for writing VBA code?
Thanks.

Report •

Related Solutions

#4
February 12, 2015 at 07:23:45
✔ Best Answer
There are lots of books and web sites related to learning to write VBA code. While I have never read a book (probably should have) I did spend a lot of time hanging out in forums and looking at what others have written, then using the debugging techniques to help understand what their code was doing, whether I needed that code or not. I'd read a question, see the answer(s) and then "debug" the code if I couldn't understand how it was doing what it was doing.

e.g. "Oh, that's how you build a comma separated text string from multiple values and then eliminate that pesky final comma!"

Once a specific technique is understood, the concept can usually be applied/expanded to fit other situations. The main thing to keep in mind is that VBA is nothing more than a logical, step-by-step set of instructions. Try recording a macro and see what the VBA editor produces. Granted, it will be inefficient, bloated code because the editor will simply record (just about) every manual step you take, but it will give you something to "debug" just to see how the debugging tools work. Eventually you will learn how to clean up recorded code to make it more efficient, but it's not a bad place to start.

Chip Pearson is probably my #1 on-line go-to resource.

http://www.cpearson.com/Excel/Topic...

Lots of formulas and VBA to play with.

I understand that John Walkenbach is a well respected author in the Excel world.

http://spreadsheetpage.com/

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 12, 2015 at 07:26:24
Excellent. Thank you. When I have a successful string of code I'll post it back (correctly).

Report •

Ask Question