-
Solved: Excel 2003 - how do I Auto Fill a Formula down to the Last Row in a Column?
Edit: Never mind, I had a typo with Range("N16").AutoFill Range("N16:J" & LastRow - 1) should be N not J
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.
[vba] 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[/vba]
Last edited by frank_m; 12-14-2010 at 06:51 PM.
-
Change [vba]LastRow = ActiveSheet.[B65536].End(xlUp).Row[/vba]
to [vba]LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).Row[/vba]
This will future proof it when you upgrade to XL2003+ (Supports over a million rows)
Cheers
Dan
-
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.
Last edited by frank_m; 12-14-2010 at 07:22 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules