没有循环的 Excel VBA 交换 vba 范围的列

Question

我有一个由两列组成的范围，用户将通过Application.Inputbox方法定义。我会将其rng存储在 VBA 中以进行复制，然后粘贴到 Excel 工作表中的某些单元格中。在粘贴之前，我想在 rng 中交换这两列。有没有办法在没有循环的情况下做到这一点，也不必交换 excel 工作表中的实际原始列？

所以我的意思是这样的：

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

Answer 1

使用变体数组作为中间临时存储，以便可以覆盖原始数组。

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

从您的叙述中，我的理解是要粘贴到的范围与要从中复制的范围不同。

所以就这样去吧

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

如何使用吉普车的代码

在玩代码时...我的好奇心消失了：

为什么不呢：？

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

事实证明，某些东西(可能(额外的行使代码变慢(大约 10%(。

我有一个类似的场景，我知道范围地址。我应该如何使用代码？

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

我忘了说我有两列以上的列要交换！？

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

你在这里有点跑题了，不是吗？

但不要到这边，到另一边，拜托！？

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

我改变主意了，我想选择一个范围，然后运行宏来移动列！？

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

我吃过了！自己做另外两个版本(Swap和ShiftLeft(！

言论

这些示例演示了如何通过进行一些简单的修改，将代码用于不同的方案。
50000 用于强调，随着范围中行数的增加，通过循环遍历范围而不是使用数组来处理初始问题的速度要慢得多。
第一个 If 语句确保范围是连续的，第二个语句确保区域中至少有两列。

问题

我不完全确定第一行中的".value"部分是不需要的，但到目前为止代码运行良好。另一方面，需要第二行中的".value"部分，否则将转移空单元格。
当范围内有公式时，它们将丢失，即值将被转移。