Vba & vlookup

Hello,

Haven't poste here in a while.

Could someone help me out with a little problem I am having.

I have written / Recorded a macro, which VLOOKUP's data from another workbook.

The amount of ROWS on each workbook changes on a daily basis. (anthing from 5 rows upto 1000+ rows)

The macro I have works fine apart from this bit.

Range("O1").Select
    ActiveCell.FormulaR1C1 = "Availability"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[availability (worst offenders).xls]Sheet1'!R2C2:R1005C6,5,FALSE)"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[availability (worst offenders).xls]Sheet1'!R2C2:R1005C6,5,FALSE)/100"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O145")
    Range("O2:O145").Select

in cell O2, I VLOOKUP the data, then doubleclick the bottom right corner of the cell to autofill down to the bottom of the list.

Of course, with the amount of rows changing on a daily basis, I need to autofill down to the bottom of the list, but I dont know how many rows there is going to be.

The current VBA code shows range O2:O145, which is the amount of rows that were there when I first recorded the macro.


Can anyone help please? :D

Any questions just ask, because I probably havent explained it that clearly.


Thanks in Advance :D