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