Copy a Non Continuous Range with Excel VBA

In Excel quite often the range you need to copy or move is not continuous. This article deals with moving ranges of differing lengths. If the ranges are the same length it can be copied from one place to another with the assistance of VBA. The following is an example of trapping Columns 2, 5 & 6 and 8 & 9. A variable is used to assign the range, note the length of the variable range is the same for all 5 Columns (Rows 11:100).


Option Explicit
Sub NonConRng() 'Excel VBA to copy a non continuous range.
Dim rng As Range

Set rng = [B11:B100, E11:F100, H11:I100]
Range("D10:D100").AutoFilter 1, "Apple"
rng.Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
End Sub


To capture data which is not continuous a Union Range can be used with a bit of help from VBA. The following example does not copy the range it changes the values in a non continuous range. You can also change the colour of the cells. The following is an example;

Option Explicit Sub URng() 'Excel VBA to change valuesa non continuous range with a union range.
Dim urng1 As Range
Dim urng2 As Range

Set urng1=Union([A10:B12], [D6:D7])
Set urng2=Union([E10:F10], [G2:G7])
Union(urng1, urng2).Value=1
End Sub


The above Excel VBA procedure will assign the value of 1 to ranges which are not of the same size and shape. Copying ranges of uneven size is not possible.