VBA - Pasting only specific columns from array

Question

following a question I have asked recently I have created an array to copy data from one worksheet to another. My problem is that the array includes many columns I do not need in the new worksheet.

x = Sheets("Sheet1").Range("A1048576").End(xlUp).Row
Set my_range = Sheets("Sheet1").Range("A2:AM" & x)
DirArray = my_range

Sheets("Sheet2").Select

Set Destination = Range("A3")
Destination.Resize(UBound(DirArray, 1), UBound(DirArray, 2)).Value = DirArray

This code copies all the data from the array but I only want specific columns from the array. How would I have to go about this to get only columns A:E, AA, L, M:K?

Any help is appreciated. Thanks in advance.

There are many ways, depending on exactly what you want to do. You could just create a results array with the columns you need. Depending on whether you want to place the results contiguously, or in the same columns as the original, you might need multiple arrays. Or you could do some timing tests to see if the four relevant copy/paste operations might run faster, and use that instead. — Ron Rosenfeld
– Ron Rosenfeld, Commented Dec 8, 2017 at 12:31

Xabier · Accepted Answer · 2017-12-08 12:46:50Z

Well if you are going to use arrays, then maybe something along these lines might help:

Sub foo()
    Dim ColumnAarray() As Variant
    Dim my_Arange As Range
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row

    'Get one column's data
    Set my_Arange = Sheets("Sheet1").Range("A2:A" & LastRow) ' get specific column
    ReDim ColumnAarray(LastRow) 'Re size your array to fit data
    ColumnAarray = my_Arange 'add data to array
    ''
    ''Repeat the process to copy more columns

    ''
    Set Destination = Sheet2.Range("A3")
    Destination.Resize(UBound(ColumnAarray, 1), UBound(ColumnAarray, 2)).Value = ColumnAarray 'add column A to Range A3 on Sheet2
End Sub

Or where you go about pasting the array values, you could change this to only paste specific columns from your original range... such as:

Sub foo()
    Dim ColumnAarray() As Variant
    Dim my_Arange As Range
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    Set my_Arange = Sheets("Sheet1").Range("A2:AM" & LastRow) ' get specific range
    ReDim ColumnAarray(LastRow) 'Re size your array to fit data
    ColumnAarray = my_Arange 'add data to array

    For i = LBound(ColumnAarray) To UBound(ColumnAarray)
        Sheet2.Range("A" & i) = ColumnAarray(i, 1) 'paste first column from original range
        Sheet2.Range("B" & i) = ColumnAarray(i, 2) 'paste second column from original range into column B in Sheet 2
    Next i
End Sub

Collectives™ on Stack Overflow

VBA - Pasting only specific columns from array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related