use of variable in Range("..,..") argument

[ghoutmann]

I have the following problem :

I am importing data in Excel and then applying a formula to each row. As the number of rows is varying I copy the formula to 20000 rows, which obviously is not the best solution.

Here’s the code I am using:



Range("L1").Select

Selection.Copy

Range("L1:L20000").Select

Range("L20000").Activate

ActiveSheet.Paste

Application.CutCopyMode = False



Range("M1").Select

Selection.Copy

Range("M1:M20000").Select

Range("M20000").Activate

ActiveSheet.Paste

Application.CutCopyMode = False



Here’s what I imaging: (code in red is made up by myself):



Range("L1").Select

Selection.Copy

'find last row in excel and store in variable LR

LR = FindLastRow

'use variable LR to define the range into which the formula will be copied

Range("L1:L&’LR’").Select

Range("L&’LR’").Activate

ActiveSheet.Paste

Application.CutCopyMode = False



As you see I have to problems:

• How to find the last row in excel and store it in a variable
• How to use a variable in the Range(“..,..”) argument

The second point interests me especially even I there is a different solution to my problem.



Thanks for your help

Gil