Set range using variables - with activecell


Posted by Kristen on October 15, 2000 8:09 PM

In the row I have 5 cells containing data which is imported from a text file. I then have a 5 contiguous cells in the row containing formulas. I want to fill the formulas down until the data cell next to the first formula cell is blank. That is,

A2:T2 contains data, U2:Z2 contains formulas
A3:T??? contains data, U3:Z??? is empty

I can get it to work as follows: test the cell next to it ie T3 if it is blank then paste. go down 1 row. loop. HOWEVER - this is very slow.
A quicker way would be to select the area that needs to be update and then paste.
So I select the last cell - then create a selection based on range("U3:" & endcell). then I paste. however this doesn't work - I tried to define the variable endcell as the activecell. I just can't seem to get it working! The endcell=activecell seems to give the cell value as the result not the range. This means that the pasterange can't evaluate correctly.

Range("U2", "Z2").Select
Selection.Copy

Dim pasterange As Range, endcell As Range

Range("Z3").Select
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Activate
Set endcell = ActiveCell
pasterange = Range("U3:" & endcell)
Range(pasterange).select
ActiveSheet.Paste



Posted by Ivan Moala on October 15, 2000 8:48 PM

Dim pasterange As Range, endcell As Range


Setting endcell = activecell is setting the variable as an object, just take it a bit further

eg endcell.Address so it reads

pasterange = Range("U3:" & endcell.Address)


Ivan