Dynamically creating Non-Contigous Ranges
 

I've got a function that identifies certain ranges that have formulas
in them and them fills them down to a certain level. Right now, I'm
getting really poor performance since I'm doing something like this:

For each rng in rngList
rng.FillDown
Next

It seems that if I do each range individually, it takes about 20-30
times longer than if I do all the ranges at once.

In the current example I'm working with, I have 20 different ranges
that need formulas filled down. When I do a multi-select & fill the
entire multi-select, it goes VERY fast.

So, I tried to build a range in the following manner:

For each rng in rngList
If strRngAddresses = "" Then
strRngAddresses = rng.Address
Else
strRngAddresses = strRngAddresses & "," & rng.Address
End If
Next

Range(strRngAddresses).FillDown

This method is very fast & it works as long as their are 14 or fewer
ranges. If there are more than 14 ranges, it fails with an error
"Method 'Range' of object '_Global' Failed.

So, is there a fast way to fill down many ranges that aren't continous?
I tried messing around with Areas, but I don't see a method for adding
an area to an existing range. Maybe I'm making this too complicated

Dynamically creating Non-Contigous Ranges
 

Dim rng1 as Range
For each rng in rngList
If rng1 is nothing Then
set rng1 = rng
Else
set rng1 = Union(rng1,rng)
End If
Next

rng1.Filldown

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
I've got a function that identifies certain ranges that have formulas
in them and them fills them down to a certain level. Right now, I'm
getting really poor performance since I'm doing something like this:

For each rng in rngList
rng.FillDown
Next

It seems that if I do each range individually, it takes about 20-30
times longer than if I do all the ranges at once.

In the current example I'm working with, I have 20 different ranges
that need formulas filled down. When I do a multi-select & fill the
entire multi-select, it goes VERY fast.

So, I tried to build a range in the following manner:

For each rng in rngList
If strRngAddresses = "" Then
strRngAddresses = rng.Address
Else
strRngAddresses = strRngAddresses & "," & rng.Address
End If
Next

Range(strRngAddresses).FillDown

This method is very fast & it works as long as their are 14 or fewer
ranges. If there are more than 14 ranges, it fails with an error
"Method 'Range' of object '_Global' Failed.

So, is there a fast way to fill down many ranges that aren't continous?
I tried messing around with Areas, but I don't see a method for adding
an area to an existing range. Maybe I'm making this too complicated