Drag cell formula down as far as the data range goes with VBA

[mike_980]

Hi everyone, I have a spreadsheet in 2003 that holds four columns of data which is a variable size range (some days it could be 25 rows, some days 5000 rows) so I created a dynamic range to run a pivot table from. This works fine, but I want to convert the time column to "Time since start" so essentially current time - origninal time, which means using a formula down the side of the pivot table. The question is, can I somehow only put this formula down as far as the pivot table runs? I have sent the formula to the bottom of the sheet for now but it makes the file huge and isn't really best practice!

[TMS]

Can we see a sample workbook and the current code, please?


Regards, TMS

[mike_980]

Book1.xls

I have had to take out 90% of the workbook to get it to a decent size, the file attached is basically what I want to do though.

The column I highlighted in yellow contains a formula to add a description to the data which comes in from a .csv file. There will be more or less records depending on how big the .csv file is and how many records there are but I have a few instances where I need to do this so dragging the formula to the end of the page makes the file in excess of 90mb which isn't ideal.

What I want is a VB script that I can assign to a button which either performs the action of the Vlookup instead of a formula, or a script which drags the formula to the bottom of the data. I hope that makes sense?

And thanks for the quick reply!

[mike_980]

As a footnote to that:

The data ranges are all dynamic in my workbook all with names (for example "CSV_import" so the VBA I guess would be something like this:

Test the size of "CSV_import"
CSV_Import = x

Drag formula down to the size of x

Obviously that is just the logic I expect it to take, my problem is i'm not sure how to go about actually writing it