Concatenating ranges 1

[bultimatem]

I have a named range of rows (say, rows C:G) and I want to take it and add row A to it so that the range now covers rows A,C,D,E,F,G. Any ideas on how to do this in VBA?
Thanks,

Bryan Marble
IEWS
BAE Systems

 

[Zathras]

Look in the Excel VBA help file with index entry "Union" and select "Referring to multiple ranges"

You can combine multiple ranges into one Range object using the Union method. The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C34, and then formats the combined ranges as bold.

Sub MultipleRange()
	Dim r1, r2, myMultipleRange As Range
	Set r1 = Sheets("Sheet1").Range("A1:B2")
	Set r2 = Sheets("Sheet1").Range("C3:D4")
	Set myMultipleRange = Union(r1, r2)
	myMultipleRange.Font.Bold = True
End Sub

 

[bultimatem]

Thanks a lot!

Bryan Marble
IEWS
BAE Systems