vlookup on a dynamic # of rows

There are two things I hope to achieve in a Macro.

1) I want to have a formula copy in a column to the exact # of rows adjacent to it. For example, I begin pasting data into column A which will be a variable # of rows, between 100 and 5000. Then I trigger a Macro that will enter a Vlookup formula in column B, then copy down as long as there is data in column A. My usual method is use an "if" statement along with Vlookup, something like "If there is a number in cell A2, then use vlookup in B2, else return no value" (something like that at least). Then I just copy this down enough rows to guarantee that it won't miss any data in column A. In my example then I would probably copy it down to row 6,000 which gives me 1,000 rows of cushion since I don't expect to every paste more than 1000 rows at a time.

So, is there another way to achieve my desired results? The problem I have with my current method is that when I am done the workbook will be larger than it really is and if I choose to print it I will get lots of blank pages, especially if the pasted file was on the small side (say 100 rows).

2) Next issue, the vlookup itself is pulling from a dynamic sized table. The table always has the same # of columns, but the rows grow/shrink (range is 4,000 to 8,000). Again, I usually just choose my table array in Vlookup to be for the max+ some safety cushion, say 9000 rows, but I was interested if there was a way to make the list dynamic.

Thanks for any help

Thanks for the tips.

Regarding autofill, in my experience that seems to be "frozen" when used in the context of a Macro.

To clarify, I am making a Macro that will do numerous things for a colleague (formatting, adding multiple Vlookup formulas, some other formulas, text-to-column breaking, etc). I am trying to make the macro as perfect as possible because this person is not very good with Excel, that is why I was trying to have the "smart" autofill.

When I currently use autofill during the macro it records the number of rows during the time of recording, and does not auto-adjust.

Could be because I am using the record function and just doubleclicking on the formulas to get them to auto-populate?

Probably, yes. Can you post the section of code where your auto populate is? I'll try and sort it out for you, if you can wait till tomorrow?

dave

Thanks, here is the section of code:

Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G357")
Range("G2:G357").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H357")
Range("H2:H357").Select
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I357")
Range("I2:I357").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J357")

As I said, I am not too good with VB so I usually just record my macros and then go back and edit using the little knowledge I do have of VB. In this example all I did was doubleclick on the cell to get it to autofill, but in the code it freezes it to the exact # of rows currently in the workbook. Tomorrow it may be 1000 rows though so I want the macro to be "smart" enough to autofill to the right number of rows each time.

thanks