how to switch rows

C

Chip Pearson

You can do it with VBA:

Dim V1 As Variant
Dim V2 As Variant
V1 = Range("1:1").Value
V2 = Range("2:2").Value
Range("1:1").Value = V2
Range("2:2").Value = V1



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
T

T Kirtley

If you want to move the entire row you can use the following sequence:

Shift^Spacebar to select a row (or several consecutive rows), then
Ctrl^X to cut the row(s),
Move to the row where you want to insert the cut row and press
Shift^Spacebar again to mark where to insert the row, and finally,
Ctrl^+ (thats the plus key on the number keypad) which moves the row(s) you
cut to the new location.

Hope that helps,

TK
 
A

analyst41

Chip said:
You can do it with VBA:

Dim V1 As Variant
Dim V2 As Variant
V1 = Range("1:1").Value
V2 = Range("2:2").Value
Range("1:1").Value = V2
Range("2:2").Value = V1



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...

Thanks that exchanges row 1 and row2. The next question would be how
can I get the macro to accept two parameters i and j and then exchange
row i and row j ?

Thanks.
 
C

Chip Pearson

Try something like

Dim V1 As Variant
Dim V2 As Variant
Dim I As Integer
Dim J As Integer
I = 5
J = 10
V1 = Range(I & ":" & I).Value
V2 = Range(J & ":" & J).Value
Range(I & ":" & I).Value = V2
Range(J & ":" & J).Value = V1



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

analyst41

T said:
If you want to move the entire row you can use the following sequence:

Shift^Spacebar to select a row (or several consecutive rows), then
Ctrl^X to cut the row(s),
Move to the row where you want to insert the cut row and press
Shift^Spacebar again to mark where to insert the row, and finally,
Ctrl^+ (thats the plus key on the number keypad) which moves the row(s) you
cut to the new location.

Hope that helps,

TK
Click to expand...

That works but its almost as much work as the dumb way (create an empty
row where you want the source row to go, copy paste the source row to
the empty target row and then delete the original source row).

But thanks any ways.
 
A

analyst41

Chip said:
Try something like

Dim V1 As Variant
Dim V2 As Variant
Dim I As Integer
Dim J As Integer
I = 5
J = 10
V1 = Range(I & ":" & I).Value
V2 = Range(J & ":" & J).Value
Range(I & ":" & I).Value = V2
Range(J & ":" & J).Value = V1
Click to expand...

Now I get "out of stack space".

Also is there any way I can invoke the macro with I and J as input
parameters ?

Thanks again. (after all there is a dumb way to do it - I just wanted
to knoe if there was a fairly short way).
 
C

Chip Pearson

Try

Sub SwapRows(I As Integer, J As Integer)
Dim V1 As Variant
Dim V2 As Variant
On Error GoTo ErrH
Application.EnableEvents = False
V1 = Range(I & ":" & I).Value
V2 = Range(J & ":" & J).Value
Range(I & ":" & I).Value = V2
Range(J & ":" & J).Value = V1
ErrH:
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top