Hi all

I am trying to build a macro that switches two rows in a worksheet.
i.e A A A A A
D D D D D
switches to
D D D D D
A A A A A
I can do it with absolute cell references but can it be done when the user
selects any two rows in a worksheet, as my way only work in the named cells?

Cheers

TM

This needs more error trapping (i.e. that the multiple areas have the same number of rows and
columns etc), and switching off events, screen updatingm=, recalculation, etc, but it may give
you some ideas:

Sub SwitchRows()
Dim Rng1 As Range
Dim Rng2 As Range
Dim v1 As Variant
Dim v2 As Variant

With Selection
If .Areas.Count = 1 Then
If .Rows.Count 1 Then
Set Rng1 = .Rows(1)
Set Rng2 = .Rows(2)
Else
MsgBox "Only one row selected", vbOKOnly
Exit Sub
End If
Else
Set Rng1 = .Areas(1).Rows(1)
Set Rng2 = .Areas(2).Rows(1)
End If
End With

v1 = Rng1.Value
v2 = Rng2.Value
Rng1.Value = v2
Rng2.Value = v1

End Sub


On Sat, 08 Nov 2003 01:19:47 GMT, "TM" wrote:

Hi all

I am trying to build a macro that switches two rows in a worksheet.
i.e A A A A A
D D D D D
switches to
D D D D D
A A A A A
I can do it with absolute cell references but can it be done when the user
selects any two rows in a worksheet, as my way only work in the named cells?

Cheers

TM

Thankyou Myrna

It works great

TM

"Myrna Larson" wrote in message
...
This needs more error trapping (i.e. that the multiple areas have the same
number of rows and
columns etc), and switching off events, screen updatingm=, recalculation,
etc, but it may give
you some ideas:

Sub SwitchRows()
Dim Rng1 As Range
Dim Rng2 As Range
Dim v1 As Variant
Dim v2 As Variant

With Selection
If .Areas.Count = 1 Then
If .Rows.Count 1 Then
Set Rng1 = .Rows(1)
Set Rng2 = .Rows(2)
Else
MsgBox "Only one row selected", vbOKOnly
Exit Sub
End If
Else
Set Rng1 = .Areas(1).Rows(1)
Set Rng2 = .Areas(2).Rows(1)
End If
End With

v1 = Rng1.Value
v2 = Rng2.Value
Rng1.Value = v2
Rng2.Value = v1

End Sub


On Sat, 08 Nov 2003 01:19:47 GMT, "TM" wrote:

Hi all

I am trying to build a macro that switches two rows in a worksheet.
i.e A A A A A
D D D D D
switches to
D D D D D
A A A A A
I can do it with absolute cell references but can it be done when the
user
selects any two rows in a worksheet, as my way only work in the named
cells?

Cheers

TM