Excel Set range using variables

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

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Set range using variables - with activecell

Re: Set range using variables - with activecell

Check out our Excel VBA Resources