I have seen many similar questions and great answers, so I am hoping someone can help me. I have a cash burn schedule that I have built and works great, but I would like to automate it even more. I would like to have it add/subtract rows with calculated formulas based on user input. At the top I input the Project Budget in F2 and the Duration in K2. Based on the Duration(K2) I would like the numbered rows beginning Row 4 to expand and contract with formulas and row numbers.

Here is the file:Construction Spend.xls

✔ Best Answer

Try this version. If it gives you problems, please post the copy of the workbook you've tried it in, leaving the code in the workbook.

Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to K2 If Target.Address = "$K$2" Then 'Find last Row with data in Column B lastRw = Range("B" & Rows.Count).End(xlUp).Row 'Delete B:E cells, leaving 3 months to use as AutoFill Range("B7:E" & lastRw).Delete shift:=xlUp 'Get number of months from K2 numMonths = Range("K2") 'AutoFill Columns B:E based on value in K2 Range("B4:E6").AutoFill Destination:=Range("B4:E" & numMonths + 3), _ Type:=xlFillDefault End If End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Is there a minimum number of months that will be entered in K2? Do the formulas currently in Rows 35 and 37 need to be placed in the same relative positions if there were more (or less) rows?

Why does the formula in E35 only sum the values in E4:E27 when you have data in E4:E33?

=SUM(E4:E27)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

@DerbyDad03, You could assign a minimum of 6 months. I think that would be about the shortest duration.

They could be moved to the side to get them out of the way. I just need some way of knowing if the standard deviation in I2 needs adjusting to get me close to the total cost.

E35 should have summed the entire column from E4 to E33...operator error.

Thanks for the quick reply, I appreciate it.

I can not download your spreadsheet at work, so I'm going off of memory here. This code will fire when you enter your Duration in K2. It will delete everything below C6:E6, leaving 3 months to use as the autofill criteria. It will then autofill Column C:E based on the Duration value in K2.

It does not do anything about writing the other formulas that are currently in Rows 35 and 37 since, frankly, I don't remember what they are.

If you'll tell me what those formulas are and where you want them placed, I can see about modifying the code to have them end up where you want.

Another option is to place them at the top of sheet, outside of Column C:E so that they are not impacted by the deletion and additon of the Rows.

If you used a Named range for e.g. Column E, you could use somthing like

=SUM(myColumnE) to sum all the values in Column E regardless of how many Rows contained data.I suggest you try this in a backup copy of your workbook since macros cannot be undone.

1 - Right click the sheet tab for the sheet you are working with.

2 - Paste this code into the pane that opens.

3 - Enter a number in K2

Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to K2 If Target.Address = "$K$2" Then 'Find last Row with data in Column B lastRw = Range("C" & Rows.Count).End(xlUp).Row 'Delete B:D cells, leaving 3 months to use as AutoFill Range("C7:E" & lastRw).Delete shift:=xlUp 'get number of months from K2 numMonths = Range("K2") 'AutoFill Columns B:D based on value in K2 Range("C4:E6").AutoFill Destination:=Range("C4:E" & numMonths + 3), _ Type:=xlFillDefault End If End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

@DerbyDad03 Ok, I think it is close but there are a couple of issues:

I relocated my validation cells, when I input a new value in K2 my validation cell does not sum all the values in Column D as it had before.

Column B also needs to auto adjust, so the month numbers coincide with the rows of data.

Also, when I enter a longer duration Column B does not auto populate with the new months. and the same issue with the validation cells.

Like I said, I can't open your spreadsheet at work so I didn't remember which columns you were using. I can look at it when I get home if you haven't fixed it by then. If you are going to use the code, you should at least understand it a little bit.

Perhaps if you looked at the code and the green comments, you might be able to edit the Column letters to match the columns you are working with. e.g where I have used C and E, change them to B and D if B:D are the columns in use.

As far as summing an entire column, did you see my comment about using a Named Range?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

@DerbyDad03 I couldn't agree more about understanding the code. Unfortunately, I tried replacing 'C' with 'B' and really screwed things up. I appreciate your help with this and it is not urgent.

I have adjust the sum to add the entire column, which works.

Thanks again.

Try this version. If it gives you problems, please post the copy of the workbook you've tried it in, leaving the code in the workbook.

Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to K2 If Target.Address = "$K$2" Then 'Find last Row with data in Column B lastRw = Range("B" & Rows.Count).End(xlUp).Row 'Delete B:E cells, leaving 3 months to use as AutoFill Range("B7:E" & lastRw).Delete shift:=xlUp 'Get number of months from K2 numMonths = Range("K2") 'AutoFill Columns B:E based on value in K2 Range("B4:E6").AutoFill Destination:=Range("B4:E" & numMonths + 3), _ Type:=xlFillDefault End If End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Perfect! Exactly what I was looking for.

Ask Your Question

Weekly Poll