Solved: Excel 2003 - how do I Auto Fill a Formula down to the Last Row in a Column?

[frank_m]

Edit: Never mind, I had a typo with Range("N16").AutoFill Range("N16:J" & LastRow - 1) should be N not J :rofl:

Corrected that and it works fine
-------------------------------------------------------------------------------------------------------
Hi,

I am creating a formula in Cell N16 and need to copy that formula down about 20,000 rows to one row less than the last row in Column N

I am using Column B as the column to determine the last row because it always has data.

I am trying to use AutoFill to perform the task, but I guess I'm using it incorrectly as it is not doing anything.

I have verified the formula is correct in cell N16 and I have verified that the Last row is determined correctly.
Dim LastRow As Long

LastRow = ActiveSheet.[B65536].End(xlUp).Row

Range("N16").FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"

Application.ScreenUpdating = False
'''''''''''''''''''''''''''''''''''''''''''''''''Edit: Solved typo by replacing the J with N
Range("N16").AutoFill Range("N16:N" & LastRow - 1) 'Range("N16").AutoFill Range("N16:J" & LastRow - 1)

Application.ScreenUpdating = True

[Blade Hunter]

Change LastRow = ActiveSheet.[B65536].End(xlUp).Row

to LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).Row

This will future proof it when you upgrade to XL2003+ (Supports over a million rows)

Cheers

Dan

[frank_m]

Hi Dan,

I had stuck in my the back of my head to stay away from count because of what I had read many months ago at http://excelusergroup.org/forums/t/314.aspx

but after looking it up now I see that is only with counting cells, not rows, as cells in 2007 and above can be over a Billion.

Thanks for the tip; I will certainly start using that. :friends: