Not doing VLOOKUP on empty cells

[Belair58]

Hi,

I'm working with data that the quantity varies from month to month. Due to Excel's issues with leading zeros I've had to cut and paste data into a sheet. Once that's done I'm doing a VLOOKUP on the data. Since I never know how many cells will have data, I've created a range of 20000 cells to do the VLOOKUP on.

Here's my code for that:

Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'transmitted pricing.xls'!C14,1,FALSE)"
    Selection.Copy
    Range("E3:E20000").Select
    ActiveSheet.Paste

What I'd like to be able to do is find a way for it to only copy the VLOOKUP code down to the last needed cell. So, if A11000 is blank, don't copy the code down any farther.
The reason for this, is I believe that it's taking much longer to run the code than it needs to, because of the extra VLOOKUP on the blank cells.

Any help would be greatly appreciated.

Thanks

 

[njimack]

Sub test()
Dim End_Row As Long

End_Row = Range("A65536").End(xlUp).Row

Range("E2").FormulaR1C1 = _ 
        "=VLOOKUP(RC[-4],'transmitted pricing.xls'!C14,1,FALSE)" 
    Range("E2").Copy Destination:=Range("E3:E" & End_Row)
Application.Cutcopymode = False
End Sub

 

[Belair58]

njimack,

Works great, thank you very much.