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).
Sub NonConRng() 'Excel VBA to copy a non continuous 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)
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;
Dim urng2 As Range
Set urng1=Union([A10:B12], [D6:D7])
Set urng2=Union([E10:F10], [G2:G7])
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.