Extending data in a Variant

[Don]

I am reading the data from a range into a variant in order to speed up
subsequent calculations. The problem is that I want to load additional data
from the end of the range into that variant. Here is my code:

Function myfunction(rng1 As Range) As String
Dim tmp_Rng1 As Variant
temp_rng1 = rng1



For example, if rng1=a1:a200 I want to add the data contained in a201 to
a220 to the end of temp_rng1. So temp_rng1 would contain all the data from
a1:a220.

I'm pretty weak on VBA, but I would guess this is pretty simple for someone
who is savvy.

Thanks,
Don

 

[Per Jessen]

Hi Don

I don't see how it would speed up calculations that you declare the variable
as Variant.
Declare Tmp_Rng1 as Range and use Resize to enlarge the range:

Function myfunction(rng1 As Range) As String
Dim tmp_Rng1 As Range
r = rng1.Rows.Count
c = rng1.Columns.Count
Set temp_rng1 = rng1.Resize(r + 20, c)

Hopes this helps.
....
Per


End Function

 

[Don]

Thank you.

Actually I need to expand an additional 20 rows of data, not columns as you
showed. However, I can now see how to expand rows.

Although you showed me how to resize the variable, I also need to know how
to load the additional data.

I am loading the original data as follows:
temp_rng1 = rng1

How do I load the additional 20 rows of data?

BTW, I am speeding up by loading the variable as opposed to previously
calculating against each cell in the range. The speed difference is
dramatic.

Thanks again,
Don

 

[Per Jessen]

Hi Don,

Try this then:

Function myfunction(rng1 As Range) As String
Dim tmp_Rng1 As Variant
r = rng1.Rows.Count
c = rng1.Columns.Count
Set rng1 = rng1.Resize(r, c + 20)
tmp_Rng1 = rng1

Regards,
Per

 

[Don]

Per,

I don't think I have done a good job of explaining my problem.

Basically I have two ranges that I want to stuff into tmp_rng1. Let's call
the two ranges rng1 and rng2.

Thanks to your help I know how to expand tmp_rng1 to the correct size, but I
don't know how to put both ranges into tmp_rng1.

In summary, I know I can load rng1 as follows:

tmp_rng1 = rng1

But how do I then load rng2?

BTW, rng2 should be loaded at the end of rng1's data in tmp_rng1

Thanks again,
Don


Hi Don,

Try this then:

Function myfunction(rng1 As Range) As String
Dim tmp_Rng1 As Variant
r = rng1.Rows.Count
c = rng1.Columns.Count
Set rng1 = rng1.Resize(r, c + 20)
tmp_Rng1 = rng1

Regards,
Per