vba - Exclude some columns while copying one row to other

Question

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.

Answer 1

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