Dragging formula to undefined length

**dhd2005** · 04-01-2010, 12:03 PM

Hi all,

I'm creating a macro to apply to a report that will change every day. One of the columns contains a formula that I'm including in the macro. I have been placing the formula in the first cell (B2), and then dragging it down to encompass all of the data in the example worksheet that I'm using, but that will change every day. Is there a way around this?

Right now I'm selecting a range that goes well over what the report should ever generate (B2:B500), but is there a way to define this by saying "B2:Whenever the data stops"?

Thanks!
David

**royUK** · 04-01-2010, 12:34 PM

Post the code that you have or an example workbook

**dhd2005** · 04-01-2010, 01:36 PM

Hi royUK,

I just attached a very simple example worksheet. Columns B and C contain LOOKUP formulas. When this report is run, Column A (automatically generated information) may have 3 items, or 5 items, or in the case of my actual spreadsheet, 150 items. What I want to do is fill in the formula in Columns B & C down to where the data ends.

So if Column A only goes to A3, automatically drag the formula in Column B to B3. If it goes to A13, B needs to go to B13. And so on.

Any suggestions?

Thanks,
David

**Palmetto** · 04-01-2010, 01:49 PM

If there are no blank cells in column-A you can use this to fill down columns-B & C for as many rows as contain entries in column-A

Please Login or Register  to view this content.

If there are blank cells in column-A, use this code.

Please Login or Register  to view this content.

**dhd2005** · 04-06-2010, 04:22 PM

Hi all,

I apologize, looking back at my example, it was profoundly unclear what I was attempting to communicate. I've uploaded a slightly different example of what I'm trying to accomplish:

Column A will populate with information of varying length. I'm inserting the function into B2, and dragging it down as far as column A has data - whether that is 13 rows or 130 rows.

My current code looks something like this:

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B14"), Type:=xlFillDefault
Range("B2:B14").Select
Range("A1").Select

Can you advise if the above is still applicable, and if so, how to apply it to my current code?

Thanks again for all of your help!

-David

**dhd2005** · 04-07-2010, 10:00 AM

Sorry to be a pest, but can anyone provide any guidance based on my last post? I'm getting some pressure to have this completed today; any additional help would be greatly appreciated! Thanks again!

**Palmetto** · 04-07-2010, 11:37 AM

Delete the existing code in the module and use this code which is slightly modified to work with your sample workbook.

Please Login or Register  to view this content.

BTW: per the Forum rules all VBA code must be wrapped in code tags. Please edit your last post and add the code tags. Click the link to view how to go about it.