How to set the range?
 

I have the data("a1:k38221"). This data keeps on fluctuating. The column L
has the Vlookup formula. Since the number of rows in data keeps on changing
evrytime I import , I need to maually set the column range in Column L to
copy the Vlookup function. Below is the program which I use to copy the
Vlookup function into Column L

range("l2").Activate
range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)"
range("l2").Select
Selection.Copy
ActiveWindow.ScrollRow = 33784
ActiveCell.range("L1:L38221").Select
ActiveSheet.Paste

So is there a way where I could set the range automatically in column L
based on data i.e instead of typing in activecell.range("L1:L38....").select
everytime in column L, can I automate th selection based on the data range

How to set the range?
 

hi
if the data is variable then the problem is to find the last row...this
time. and then again next time.
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox lr ' test purposes only
then assing the lr to the range
Range("L1:L" & lr).Select

accually you need to start avoiding the select function whenever you can.
slows things down, causes extra typing and restricts you to activesheet.
instead of ....
range("l2").Select
Selection.Copy
use...
range("l2").Copy
instead of....
ActiveCell.range("L1:L38221").Select
ActiveSheet.Paste
use...
range("L1:L" & lr).PasteSpecial xlpasteall

regards
FSt1


"Abhinandan" wrote:

I have the data("a1:k38221"). This data keeps on fluctuating. The column L
has the Vlookup formula. Since the number of rows in data keeps on changing
evrytime I import , I need to maually set the column range in Column L to
copy the Vlookup function. Below is the program which I use to copy the
Vlookup function into Column L

range("l2").Activate
range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)"
range("l2").Select
Selection.Copy
ActiveWindow.ScrollRow = 33784
ActiveCell.range("L1:L38221").Select
ActiveSheet.Paste

So is there a way where I could set the range automatically in column L
based on data i.e instead of typing in activecell.range("L1:L38....").select
everytime in column L, can I automate th selection based on the data range

How to set the range?
 

Hi

Your suggestion works like magic.
thanks very much.

"FSt1" wrote:

hi
if the data is variable then the problem is to find the last row...this
time. and then again next time.
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox lr ' test purposes only
then assing the lr to the range
Range("L1:L" & lr).Select

accually you need to start avoiding the select function whenever you can.
slows things down, causes extra typing and restricts you to activesheet.
instead of ....
range("l2").Select
Selection.Copy
use...
range("l2").Copy
instead of....
ActiveCell.range("L1:L38221").Select
ActiveSheet.Paste
use...
range("L1:L" & lr).PasteSpecial xlpasteall

regards
FSt1


"Abhinandan" wrote:

I have the data("a1:k38221"). This data keeps on fluctuating. The column L
has the Vlookup formula. Since the number of rows in data keeps on changing
evrytime I import , I need to maually set the column range in Column L to
copy the Vlookup function. Below is the program which I use to copy the
Vlookup function into Column L

range("l2").Activate
range("l2").FormulaR1C1 = "=VLOOKUP(RC[-1],EDI_PCAT!R1C3:R318C4,2,FALSE)"
range("l2").Select
Selection.Copy
ActiveWindow.ScrollRow = 33784
ActiveCell.range("L1:L38221").Select
ActiveSheet.Paste

So is there a way where I could set the range automatically in column L
based on data i.e instead of typing in activecell.range("L1:L38....").select
everytime in column L, can I automate th selection based on the data range