MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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

Dim pasterange As Range, endcell As Range

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

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)