Articles

swap 2 rows in excel

June 18, 2009 at 02:48:52
Specs: Windows Vista

how do you swap 2 rows in excel 2007
i need a macro to pick 2 rows then swap then over

See More: swap 2 rows in excel

Report •


#1
June 18, 2009 at 03:57:29

A quick answer is to record a macro as you do a "Cut - Insert Cut Cells" for the 2 rows that you want to swap.

Every time you run the macro, those 2 rows will swap.

Other than that, we're going to need some more detail.


Report •

#2
June 18, 2009 at 07:28:57

This will ask for the rows to swap.

Make sure you enter the lower row number first.

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

Macro writing tip:

Record what you want to do and then clean up/modify the code produced by the recorder.


Report •

#3
June 18, 2009 at 08:30:44

hi DerbyDad03
tried your macro swaps rows then puts them back again
can you stop them from going back

thanks robsoft


Report •

Related Solutions

#4
June 18, 2009 at 10:56:16

your macro swaps rows then puts them back again

Not exactly.
If the rows are adjacent then the macro works.
If the rows are not adjacent then the macro does not work.

MIKE

http://www.skeptic.com/


Report •

#5
June 18, 2009 at 12:06:41

I'm not sure what you guys are doing, but here's what happens when I use my code:

I start with this:

	
      A
1   One
2   Two
3   Three
4   Four
5   Five
6   Six

I run my code (copied directly from this forum) and enter 2 for the Lower Row and 5 for the Higher Row.

I end up with this:

       A
1   One
2   Five
3   Three
4   Four
5   Two
6   Six

Didn't I just swap rows 2 and 5?


Report •

#6
June 18, 2009 at 12:29:01

I got it to work, but had to change the code to:

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

End Sub


As I said in Response #4
If the rows are adjacent then the macro works.

What happened when you tried to swap non adjacent rows, is it would move the lower row up one but do nothing with the upper row.

EDIT:

I think we got Upper & Lower confused....
I was thinking Lower Row meant "lower down the sheet" not Lower Numbered Row. :-)

That's why mine worked for me the way I was working.


MIKE

http://www.skeptic.com/


Report •

#7
June 18, 2009 at 13:04:45

So, just to clarify for the OP:

With "Lower Row" meaning "Lower Numbered Row", then my original code works - without modification, correct?



Report •

#8
June 18, 2009 at 14:17:42

With "Lower Row" meaning "Lower Numbered Row", then my original code works - without modification, correct?

Correct.

MIKE

http://www.skeptic.com/


Report •

#9
June 19, 2009 at 00:17:25

thanks for helping got it working ok now
it was something in my else in my sheet
messing it up working fine now

thank you


Report •


Ask Question