repeat procedure with new range
 

hello,
Can you help? I would like to repeat this procedure by giving X1 a new range
definition each time (X1=range("country1"), X1=range("country2"), etc)

Union(X1, _
Range(X1.Offset(3, 3), X1.Offset(18, 14)), _
Range(X1.Offset(43, 3), X1.Offset(52, 14)), _
Range(X1.Offset(71, 3), X1.Offset(80, 14)), _
Range(X1.Offset(92, 3), X1.Offset(101, 14)), _
Range(X1.Offset(174, 3), X1.Offset(193, 14)), _
Range(X1.Offset(224, 4), X1.Offset(225, 14))).ClearContents

Any idea?Thanks
--
caroline

repeat procedure with new range
 

Caroline,
How about the following

Sub Test()
Dim i%
Dim x as Range
Dim asRangeNames(1 to 10)
asRangeNames(1)="Country1"
asRangeNames(2)="Country2"
'and so on

for i = 1 to ubound(asRangeNames)

set x = Range(asRangeNames(i))

Union(X1, _
Range(X1.Offset(3, 3), X1.Offset(18, 14)), _
Range(X1.Offset(43, 3), X1.Offset(52, 14)), _
Range(X1.Offset(71, 3), X1.Offset(80, 14)), _
Range(X1.Offset(92, 3), X1.Offset(101, 14)), _
Range(X1.Offset(174, 3), X1.Offset(193, 14)), _
Range(X1.Offset(224, 4), X1.Offset(225, 14))).ClearContents

Next i

End Sub

"caroline" wrote:

hello,
Can you help? I would like to repeat this procedure by giving X1 a new range
definition each time (X1=range("country1"), X1=range("country2"), etc)

Union(X1, _
Range(X1.Offset(3, 3), X1.Offset(18, 14)), _
Range(X1.Offset(43, 3), X1.Offset(52, 14)), _
Range(X1.Offset(71, 3), X1.Offset(80, 14)), _
Range(X1.Offset(92, 3), X1.Offset(101, 14)), _
Range(X1.Offset(174, 3), X1.Offset(193, 14)), _
Range(X1.Offset(224, 4), X1.Offset(225, 14))).ClearContents

Any idea?Thanks
--
caroline

repeat procedure with new range
 

It works thanks a lot
--
caroline


"Alok" wrote:

Caroline,
How about the following

Sub Test()
Dim i%
Dim x as Range
Dim asRangeNames(1 to 10)
asRangeNames(1)="Country1"
asRangeNames(2)="Country2"
'and so on

for i = 1 to ubound(asRangeNames)

set x = Range(asRangeNames(i))

Union(X1, _
Range(X1.Offset(3, 3), X1.Offset(18, 14)), _
Range(X1.Offset(43, 3), X1.Offset(52, 14)), _
Range(X1.Offset(71, 3), X1.Offset(80, 14)), _
Range(X1.Offset(92, 3), X1.Offset(101, 14)), _
Range(X1.Offset(174, 3), X1.Offset(193, 14)), _
Range(X1.Offset(224, 4), X1.Offset(225, 14))).ClearContents

Next i

End Sub

"caroline" wrote:

hello,
Can you help? I would like to repeat this procedure by giving X1 a new range
definition each time (X1=range("country1"), X1=range("country2"), etc)

Union(X1, _
Range(X1.Offset(3, 3), X1.Offset(18, 14)), _
Range(X1.Offset(43, 3), X1.Offset(52, 14)), _
Range(X1.Offset(71, 3), X1.Offset(80, 14)), _
Range(X1.Offset(92, 3), X1.Offset(101, 14)), _
Range(X1.Offset(174, 3), X1.Offset(193, 14)), _
Range(X1.Offset(224, 4), X1.Offset(225, 14))).ClearContents

Any idea?Thanks
--
caroline