Excel Copy non-Adjacent Columns

The following Excel VBA procedure will trap the last used row in a column and use this to trap all of the columns we want to copy or manipulate.  The Excel example is helpful if you have non-adjacent columns you wish to exclude when you are transferring data between sheets, workbooks or just manipulating data.  The following is an example from the Excel file below, with rows in Blue to be excluded.

Copy non adjacent colunns

The following will copy the data from A to C and F to K from one sheet to another  Excel will leave all of the data from C to E. 

Sub TrapwithIndirect() 'Excel VBA to copy non adjacent columns
Dim lr As Long

lr = [a65536].End(xlUp).Row

Range("A6:A" & lr).AutoFilter 1, "Fruit"
Intersect(Range(Cells(7, 1), Cells(lr, 1)).EntireRow, Range("A:B,F:K")).Copy Sheet2.[a2]
[A6].AutoFilter
End Sub

In the example the column headers are in row 6 and the copy starts from row 7.  It uses a filter as this isolates data very quickly.  More articles on filters with VBA can be found here;

Autofilter with VBA

Autofilter Between Dates

Autofilter Multiple Conditions

The attached worbook shows how the Excel VBA procedure using a simple example.