Copy a range to clipboard

Question

I'm trying to copy a range to the clipboard in visual basic. Some of the columns in the range should be blank. i've implemented the blank columns using an array, but in the line objData.SetText Join(dataArr(i, j - 1), ",") i get a Run-time error'9': Subscript out of range. Thanks in advance!

    Dim rangeData As Range
    Dim dataArr() As Variant
    Dim i As Long
    Dim j As Long
    
    Set rangeData = targetSheet.Range("B2:G25")
    
    ReDim dataArr(1 To rangeData.Rows.Count, 1 To rangeData.Columns.Count + 5)
    
    For i = 1 To rangeData.Rows.Count
        For j = 1 To rangeData.Columns.Count + 5
            If j < 2 Then
                'Debug.Print rangeData(i, j)
                dataArr(i, j) = rangeData.Cells(i, j)
                'Debug.Print dataArr(i, j)
            ElseIf j = 2 Or j = 6 Or j = 7 Or j = 8 Or j = 9 Then
                dataArr(i, j) = ""
            ElseIf j = 3 Then
                dataArr(i, j) = rangeData.Cells(i, j - 1)
            ElseIf j = 4 Then
                dataArr(i, j) = rangeData.Cells(i, j - 1)
            ElseIf j = 5 Then
                dataArr(i, j) = rangeData.Cells(i, j - 1)
            ElseIf j = 10 Then
                dataArr(i, j) = rangeData.Cells(i, j - 5)
            ElseIf j = 11 Then
                dataArr(i, j) = rangeData.Cells(i, j - 5)
            End If
        Next j
        ' Debug.Print dataArr(i, 4)
        ' Debug.Print dataArr(i, 2); dataArr(i, 5); dataArr(i, 6); dataArr(i, 7); dataArr(i, 8)
    Next i
    
    Dim objData As New MSForms.DataObject
    objData.SetText Join(dataArr(i, j - 1), ",")
    objData.PutInClipboard

I've tried using a function that creates a new temporary worksheet, paste the values in and copies them to the clipboard, but i find using an array that includes the column with values together with the blank columns easier and faster to use.

Answer 1

Here's a different approach (with a larger input range to satisfy skipping columns 7,8,9)

Sub Tester()

    Dim dataArr() As Variant, txt As String
    Dim r As Long, c As Long
    
    dataArr = ActiveSheet.Range("B2:M25").Value
    
    For r = 1 To UBound(dataArr, 1)
        If r > 1 Then txt = txt & vbLf
        For c = 1 To UBound(dataArr, 2)
            txt = txt & IIf(c > 1, ",", "")
            Select Case c
                Case 2, 6, 7, 8, 9
                    txt = txt & ""
                Case Else
                    txt = txt & dataArr(r, c)
            End Select
        Next c
    Next r
    
    Dim objData As New MSForms.DataObject
    objData.SetText txt
    objData.PutInClipboard

End Sub