VBA dynamic range for formulas

Question

I've posted a snippet of my code below. My actual workbook has a lot more formulas, but they are all structured like the formulas below. The amount of data I'm working with can change on a month to month basis, so I never know how many rows I will need the formulas to carry down. My little cheat for this was to make the destination range for all the formulas "XX2:XX500", because there will never be more than 500 rows.

'November Remaining Spending Plan
 Range("BF2").Select
 ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-43]+RC[-40]+RC[-37]+RC[-34]+RC[-31]+RC[-28]+RC[-25]+RC[-22])"
 Selection.AutoFill Destination:=Range("BF2:BF500")

'December Remaining Spending Plan
 Range("BG2").Select
 ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-41]+RC[-38]+RC[-35]+RC[-32]+RC[-29]+RC[-26]+RC[-23])"
 Selection.AutoFill Destination:=Range("BG2:BG500")

I would like to make the range dynamic so I can remove the

"IF(RC[-1]="""","""","

part of the formula and having the extra formulas in the workbook isn't ideal for pivot tables and other users.

I've tried playing with the ranges using combinations of LastRow, FillDown, CurrentRegion, but I haven't had any luck.

Each formula should have the same amount of rows as all other formulas and the amount of formulas should always equal the number of rows in column A minus 1 (the minus 1 is for the header).

Is there a way to clean this up?

Thanks!

Scott Craner Scott Craner · Accepted Answer · 2017-08-23T18:30:04

Dim lastRow As Long
Dim ws As Worksheet
'Always set the parent of all Range Objects
Set ws = ActiveSheet 'This should be called by name: Worksheets("Sheet1")

' this assumes column "A" is the column which sets the lastrow.
'If a different column sets the end of the dataset change "A" to that column
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'do not use .Select or .Activate it slows the code
'You do not need autofill just put it in all the cells at once


'November Remaining Spending Plan
 ws.Range("BF2:BF" & lastRow).FormulaR1C1 = "=RC[-43]+RC[-40]+RC[-37]+RC[-34]+RC[-31]+RC[-28]+RC[-25]+RC[-22]"

'December Remaining Spending Plan
 ws.Range("BG2:BG" & lastRow).FormulaR1C1 = "RC[-41]+RC[-38]+RC[-35]+RC[-32]+RC[-29]+RC[-26]+RC[-23]"

VBA dynamic range for formulas

2 Answers