在将一行复制到其他列时排除一些列 [英] Exclude some columns while copying one row to other

问题描述

我想将Excel中的一行的内容复制到其他行。

目前，我正在使用以下代码复制上一行的数据。

  rngCurrent.Offset（-1）.Copy 
 rngCurrent.PasteSpecial（xlPasteValues）
  

示例：

假设以下是行中的数据。

 要复制的行........> 1 2 3 4 5 6 7 8 .... 14 15 16 
复制前的目标行..> A B C D E F G H .... N O P 
复制后的目标行...> 1 2 3 D 5 6 7 8 .... N 15 16 
  

请注意，目标行第4列和第14列中的原始值D和N将保留。

解决方案

p>

我不确定要使用宏的方式（例如，您是否选择了单页格式的单元格或单元格），但以下代码可能会让您开始： p>

编辑 - 代码更新以反映您的意见。我添加了一个函数来检查你想保留的列是否在数组中。

  Sub SelectiveCopy（）
'根据工作表中的选定范围设置范围
 
 Dim rngCurrent As Range 
设置rngCurrent =选择
 
'定义不想复制的列 - 这里列4和14 
 
 Dim RemoveColsIndex As Variant 
 RemoveColsIndex = Array（4，14）
 
'循环复制范围并检查列是否在数组中
 
 Dim iArray As Long 
 Dim iCell As Long 
 
对于iCell = 1 To rngCurrent.Cells.Count 
如果不是IsInArray（RemoveColsIndex，iCell）然后
 rngCurrent.Cells（iCell）.Value = rngCurrent.Cells（iCell）.Offset（-1，0）
 End If 
 Next iCell 
 
 End Sub 
 
函数IsInArray（MyArr As Variant，valueToCheck As Long）As Boolean 
 Dim iArray As Long 
 
 For iArray = LBound（MyArr）To UBound（MyArr）
如果valueToCheck = MyArr（iArra y）然后
 IsInArray = True 
退出函数
结束如果
下一个iArray 
 
 InArray = False 
结束函数
  

根据你想做什么，你可以增加这个代码。例如，而不是选择要复制的范围，您可以单击行中的任何单元格，然后使用以下选项来选择 EntireRow ，然后执行复制操作：

 设置rngCurrent = Selection.EntireRow 
  

希望这有助于

I want to copy contents of one row in Excel to other row.

Currently, I am using following code for copying data from previous row.

rngCurrent.Offset(-1).Copy
rngCurrent.PasteSpecial (xlPasteValues)

but I want to skip some columns. So let's say if there are 20 columns, I want to copy all columns except column 4 and 14. How can this be achieved in VBA?

Example:

Assume following is the data in row.

Row to be copied........> 1 2 3 4 5 6 7 8 .... 14 15 16  
Target Row Before Copy..> A B C D E F G H .... N  O   P
Target Row After Copy...> 1 2 3 D 5 6 7 8 .... N  15 16  

So everything is copied except column 4 and 14. Note that original values D and N in column 4 and 14 of Target row are preserved.

解决方案

Sam

I am not sure exactly how you want to use the macro (i.e. do you select range in sheet, or single cell?) but the following code may get you started:

EDIT - code updated to reflect your comments. I have added a function to check if the columns you want to keep are in the array.

Sub SelectiveCopy()
'Set range based on selected range in worksheet

    Dim rngCurrent As Range
    Set rngCurrent = Selection

'Define the columns you don't want to copy - here, columns 4 and 14

    Dim RemoveColsIndex As Variant
    RemoveColsIndex = Array(4, 14)

'Loop through copied range and check if column is in array

Dim iArray As Long
Dim iCell As Long

For iCell = 1 To rngCurrent.Cells.Count
    If Not IsInArray(RemoveColsIndex, iCell) Then
        rngCurrent.Cells(iCell).Value = rngCurrent.Cells(iCell).Offset(-1, 0)
    End If
Next iCell

End Sub

Function IsInArray(MyArr As Variant, valueToCheck As Long) As Boolean
Dim iArray As Long

    For iArray = LBound(MyArr) To UBound(MyArr)
        If valueToCheck = MyArr(iArray) Then
            IsInArray = True
            Exit Function
        End If
    Next iArray

InArray = False
End Function

Depending on what you want to do you could augment this code. For example, rather then selecting the range you want to copy, you could click any cell in the row and then use the following to select the EntireRow and then perform the copy operation:

Set rngCurrent = Selection.EntireRow

Hope this helps

这篇关于在将一行复制到其他列时排除一些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！