Code for Autofill

duplinguy

Board Regular
Joined
Apr 25, 2002
Messages
95
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top