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.
Dim pasterange As Range, endcell As Range
Do While ActiveCell <> ""
Set endcell = ActiveCell
pasterange = Range("U3:" & endcell)