Code for Autofill

[duplinguy]

Each day I work with a file containing sales data (the number of rows varies by day). Column A has sales info, Column B contains a formula I need to use. I double click the fill handle to copy down the formula. That's easy enough but is there a way to code this to take into account a varying number of rows?

[Damon Ostrander]

Hi duplinguy,

Try this macro.

Sub FillColB()
'Fills down column B to accommodate new data added to the end of column A
Dim rngFill As Range
Set rngFill = Range([b65536].End(xlUp), [a65536].End(xlUp).Offset(0, 1))
[b65536].End(xlUp).AutoFill rngFill
End Sub

[duplinguy]

Thanks that works great!
Here's another twist to my question.
Some of my sales data does not need a calculation. So I sort and separate what does and does not need a formula with a few blank rows. The data that doesn't is below the data that does. I can't delete any rows (it's needed for future reference).
Is there a way to make your macro stop when it encounters the blank rows separating the data?

[Damon Ostrander]

Hi again Duplinguy,

Yes, the code I provided before looks from the last row up to find the last row with data. Here is code that looks from the first row down. This stops when it encounters a row without data. It starts on the second row assuming that the first row contains headers or might be blank.


Sub FillColB()
'Fills down column B to accommodate new data added to the end of column A
Dim rngFill As Range
Set rngFill = Range([b2].End(xlDown), [a2].End(xlDown).Offset(0, 1))
[b2].End(xlDown).AutoFill rngFill
End Sub

[duplinguy]

Help! It doesn't seem to be working.
Sales data is in Column A starting on row 2. The formula I want to autofill is in cell B2.
By the way, the screen flickers like something may be happening.

[Damon Ostrander]

Hi again duplinguy,

The code I provided works fine in my workbook. I'm wondering if you have some stray data somewhere in columns A or B. How about checking these columns for such stray data by putting your cursor at the end of data in each of these columns and doing a Ctrl-down arrow. If there is no stray data below that point Excel should immediately select A65536 or B65536, depending on column A or B. If it stops on some other cell, this is undoubtedly the problem. Do a clear contents on all such cells.

Damon