Excel VBA swapping columns of a vba range without a loop

Question

I have a range consisting of two columns that the user would define thru Application.Inputbox method. I would store that as rng in the VBA to be copied then pasted later to some cells in Excel sheet. Before pasting, I would like to swap these two columns in rng. Is there a way to do that without a loop and without having to swap the actual original columns in the excel sheet?

So what I mean is something like this:

rng_swapped.Columns(1).Value = rng.Columns(2).Value
rng_swapped.Columns(2).Value = rng.Columns(1).Value

rng = rng_swapped

Answer 1

Use a variant array as an intermediate temporary storage so you can overwrite the original.

dim arr as variant

arr = rng_swapped.Columns(1).value
rng_swapped.Columns(1) = rng_swapped.Columns(2).Value
rng_swapped.Columns(2) = arr

Answer 2

from your narrative my understanding is that the range to paste to is different from the range to copy from.

so just go like this

Dim rng As Range
Set rng = Application.InputBox("Please select a range:", "Range Selection", , , , , , 8)

Dim rngToPaste As Range
Set rngToPaste = rng.Offset(, 20) ' just a guess...

rngToPaste.Columns(1).Value = rng.Columns(2).Value
rngToPaste.Columns(2).Value = rng.Columns(1).Value

Answer 3

How to use Jeeped's code

While playing around with the code... my curiosity fires away:

Why not:?

  arr1 = oRng.Columns(1)
  arr2 = oRng.Columns(2)
  oRng.Columns(1) = arr2
  oRng.Columns(2) = arr1

It turns out something (probably) the extra line makes the code slower (by about 10%).

I have a similar scenario and I know the range address. How should I use the code?

Sub SwapColumnsRange()
'Description
  'In a specified range, swaps the first two columns i.e. the values of
  'column(1) become the values of column(2) and the values of column(2) become
  'the values of column(1).
'Arguments as constants
  'cStrRange
    'A string containing the Address of the range to be processed.

  Const cStrRange As String = "A1:B50000" 'Your range address here.

  Dim arr As Variant
  Dim oRng As Range

  Set oRng = Range(cStrRange)

    If oRng.Areas.Count > 1 Then Exit Sub
    If oRng.Columns.Count < 2 Then Exit Sub

  'Slightly modified Jeeped's code
  arr = oRng.Columns(1) '.Value
  oRng.Columns(1) = oRng.Columns(2).Value
  oRng.Columns(2) = arr

End Sub

I forgot to mention that I have more than two columns to be swapped!?

Sub ShiftColumnsRangeLeft()
'Description
  'In a specified range with columns from 1 to 'n', shifts columns to the left
  'i.e. the values of column(1) become the values of column(n), the values of
  'column(2) become the values of column(1)... ...the values of column(n), the
  'last column, become the values of column(n-1).
'Arguments as constants
  'cStrRange
    'A string containing the Address of the range to be processed.

  Const cStrRange As String = "A1:I50000" 'Your range address here.

  Dim arr As Variant
  Dim oRng As Range
  Dim i As Integer

  Set oRng = Range(cStrRange)

    If oRng.Areas.Count > 1 Then Exit Sub
    If oRng.Columns.Count < 2 Then Exit Sub

  For i = 1 To oRng.Columns.Count - 1 'ShiftColumnsRangeRight Difference
  'Slightly modified Jeeped's code
    arr = oRng.Columns(i) '.Value
    oRng.Columns(i) = oRng.Columns(i + 1).Value
    oRng.Columns(i + 1) = arr
  Next

End Sub

You're a little off topic here, aren't you?

But not to this side, to the other side, please!?

Sub ShiftColumnsRangeRight()
'Description
  'In a specified range with columns from 1 to 'n', shifts columns to the right
  'i.e. the values of column(1) become the values of column(2), the values of
  'column(2) become the values of column(3)... ...the values of column(n), the
  'last column, become the values of column(1).
'Arguments as constants
  'cStrRange
    'A string containing the Address of the range to be processed.

  Const cStrRange As String = "A1:I50000" 'Your range address here.

  Dim arr As Variant
  Dim oRng As Range
  Dim i As Integer

  Set oRng = Range(cStrRange)

    If oRng.Areas.Count > 1 Then Exit Sub
    If oRng.Columns.Count < 2 Then Exit Sub

  For i = oRng.Columns.Count - 1 To 1 Step -1 'ShiftColumnsRangeLeft Difference
  'Slightly modified Jeeped's code
    arr = oRng.Columns(i) '.Value
    oRng.Columns(i) = oRng.Columns(i + 1).Value
    oRng.Columns(i + 1) = arr
  Next

End Sub

I've changed my mind, I want to select a range and then run the macro to shift the columns!?

Sub ShiftColumnsSelectionRight()
'Description
  'In a selection with columns from 1 to 'n', shifts columns to the right
  'i.e. the values of column(1) become the values of column(2), the values of
  'column(2) become the values of column(3)... ...the values of column(n), the
  'last column, become the values of column(1).

  Dim arr As Variant
  Dim oRng As Range
  Dim i As Integer

  Set oRng = Selection

    If oRng.Areas.Count > 1 Then Exit Sub
    If oRng.Columns.Count < 2 Then Exit Sub

  For i = oRng.Columns.Count - 1 To 1 Step -1 'ShiftColumnsRangeLeft Difference
  'Slightly modified Jeeped's code
    arr = oRng.Columns(i) '.Value
    oRng.Columns(i) = oRng.Columns(i + 1).Value
    oRng.Columns(i + 1) = arr
  Next

End Sub

I've had it! Do the other two versions (Swap & ShiftLeft) yourself!

Remarks

These examples demonstrate how by making some simple modifications, the code can be used in different scenarios.
50000 is used to emphasize that the handling of the initial problem by looping through the range instead of using an array gets much, much slower as more rows are in the range.
The first If Statement ensures that the range is contiguous, and the second one ensures that there are at least two columns in the range.

Issues

I'm not completely sure that the '.value' part in the first line is not needed, but the code worked fine so far. On the other hand the '.value' part in the second line is needed or empty cells will be transferred.
When there are formulas in the range, they will be lost i.e. values will be transferred instead.