Swapping two ranges

Question

I am trying to write code for swapping two ranges:

Sub SwapRanges()
  Dim range1 As Range
  Dim range2 As Range
  Dim holder As Range

  range1 = Range("D7:D12")
  range2 = Range("E7:E12")
  holder = Range("F7:F12")

  Set holder = range1
  Set range1 = range2
  Set range2 = holder
End Sub

but get run-time error 91.

Answer 1

The cause of the error is that you are not using the Set command when assigning ranges. In addition, understand that this code:

Set holder = range1
Set range1 = range2
Set range2 = holder

only changes the ranges referred to by the variables holder, range1, and range2. It doesn't actually copy the data contained in those references from one range to another.

The code below will perform the actual copying while also addressing the run-time error.

Sub SwapRanges()
  Dim range1 As Range
  Dim range2 As Range
  Dim holder As Range

  Set range1 = Range("D7:D12")
  Set range2 = Range("E7:E12")
  Set holder = Range("F7:F12")

  range1.Copy holder
  range2.Copy range1
  holder.Copy range2
End Sub

Answer 2

I would try this way (sorry, non tested - writing from a Chrombook :)

Sub SwapRanges()
  Dim range1 As Range
  Dim range2 As Range
  Dim holder() as variant

  Set range1 = Range("D7:D12")
  Set range2 = Range("E7:E12")

  holder = range1    
  range1 = range2
  range2 = holder
End Sub

Answer 3

Instead of coping you can just say: holder=range1 Range2=range1 Holder=range2

Not realy related to the question pnuts answered it but I thought this would be helpful for your application.