Auto add/delete rows based on specific cell value.

[Sean15]

Hi:
I want to add rows to table below based on valued in G2. So, if G2 has 61, table should have 61 rows starting from E10, if 41 in G2, table should have 41 rows, etc.

I also want to keep the subtotals as shown in F61 and G61 but subtotal should adjust up/down based on value in G2. I have searched online but can’t find codes that does that. Also, I read that AI tools have just as much VB skills as I do so I better ask the professionals.

Can someone help?

PV of lease pymts.xlsm
	E	F	G
1
2		Number of Periods	51
3
4
5
6		Annual Rate	9.00%
7
8
9	Period	Cash	Present Value
10	1	$237,651.19	$235,882.07
11	2	$237,651.19	$234,126.12
12	3	$237,651.19	$232,383.25
13	4	$237,651.19	$230,653.35
14	5	$237,651.19	$228,936.33
15	6	$237,651.19	$227,232.09
16	7	$237,651.19	$225,540.53
17	8	$237,651.19	$223,861.57
18	9	$244,780.72	$228,860.96
19	10	$244,780.72	$227,157.28
20	11	$244,780.72	$225,466.28
21	12	$244,780.72	$223,787.87
22	13	$244,780.72	$222,121.96
23	14	$244,780.72	$220,468.45
24	15	$244,780.72	$218,827.24
25	16	$244,780.72	$217,198.26
26	17	$244,780.72	$215,581.39
27	18	$244,780.72	$213,976.57
28	19	$244,780.72	$212,383.69
29	20	$244,780.72	$210,802.67
30	21	$252,124.14	$215,510.42
31	22	$252,124.14	$213,906.13
32	23	$252,124.14	$212,313.78
33	24	$252,124.14	$210,733.28
34	25	$252,124.14	$209,164.54
35	26	$252,124.14	$207,607.49
36	27	$252,124.14	$206,062.02
37	28	$252,124.14	$204,528.06
38	29	$252,124.14	$203,005.52
39	30	$252,124.14	$201,494.31
40	31	$252,124.14	$199,994.35
41	32	$252,124.14	$198,505.56
42	33	$259,687.87	$202,938.69
43	34	$259,687.87	$201,427.98
44	35	$259,687.87	$199,928.52
45	36	$259,687.87	$198,440.21
46	37	$259,687.87	$196,962.99
47	38	$259,687.87	$195,496.77
48	39	$259,687.87	$194,041.45
49	40	$259,687.87	$192,596.98
50	41	$259,687.87	$191,163.25
51	42	$259,687.87	$189,740.20
52	43	$259,687.87	$188,327.74
53	44	$259,687.87	$186,925.80
54	45	$267,478.50	$191,100.32
55	46	$267,478.50	$189,677.74
56	47	$267,478.50	$188,265.74
57	48	$267,478.50	$186,864.26
58	49	$267,478.50	$185,473.21
59	50	$267,478.50	$184,092.52
60	51	$267,478.50	$182,722.10
61	Total	$12,852,671.76	$10,604,259.89
PV_Calc

Cell Formulas
Range	Formula
E11:E59	E11	=IF($G$2>E10,E10+1,"")
E60	E60	=IF($G$2>=E59,E59+1,"")
G10:G60	G10	=IF(OR(E10="",F10=""),"",PV($G$6/12,E10,0,-F10,0))
F61	F61	=SUBTOTAL(109,[Cash])
G61	G61	=SUBTOTAL(109,[Present Value])

Cells with Data Validation
Cell	Allow	Criteria
G2	Whole number	between 1 and 1200

 

[Sean15]

Thank you very much.

 

[Sean15]

Hi:
Sorry to get back to you on this.
I wanted to adjust code in post #4, so it won't do any calculation or add number sequence in column E.
I delete line:

.DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange.Columns(1), xlFillSeries

but code is still adding 1 in column E. What else do I have to delete so code in post #4 won't do any calculation or add number in column E.

 

[Akuini]

but code is still adding 1 in column E. What else do I have to delete so code in post #4 won't do any calculation or add number in column E.

What is the initial value in E10?
Do you have formula in E11 downward (as shown in your original example)? And do you want to keep that formula?

 

[Sean15]

Hi:

I changed formula slight. Please see below.

PV of lease pymts-Updated VBA codes - Copy.xlsm
	E
11	Period
12	0
13	1
14	2
15	3
PV_Calc

Cell Formulas
Range	Formula
E12	E12	=IF($G$8="Beginning",0,1)
E13:E15	E13	=E12+1

I thought I could just add formula in E12 (row changed because I added two new rows to worksheet), then E13 downwards but there is something in your code that replaces the formulas I just added.

 

[Akuini]

Hi:
I thought I could just add formula in E12 (row changed because I added two new rows to worksheet), then E13 downwards but there is something in your code that replaces the formulas I just added.

Replace this part:

                .DataBodyRange.Cells(2, 1).AutoFill .DataBodyRange.Columns(1), xlFillSeries

with this:

               .DataBodyRange.Cells(2, 1).Copy .DataBodyRange.Cells(2, 1).Resize(n - 1)

 

[Sean15]

Thank you.
Your help is greatly appreciated.

Sean

 

[Akuini]

Try this one, it is faster than the previous one:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
   
    Dim n As Long, i As Long, h As Long
    Dim tb As ListObject
    Dim c As Range
    
    n = Target.Value
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManual
    Set tb = ActiveSheet.ListObjects(1)
    With tb
           h = .DataBodyRange.Rows.Count
            If h < n Then  'add rows
                Set c = .DataBodyRange.Cells(1).Offset(h).Resize(n - h, 3)
                c.Insert shift:=xlShiftDown
                .Resize .Range.Cells(1).Resize(n + 1, 3)
                .DataBodyRange.Cells(2, 1).Copy .DataBodyRange.Cells(2, 1).Resize(n - 1)
            ElseIf h > n Then  'delete rows
               .Resize .Range.Cells(1).Resize(n + 1, 3)
               .DataBodyRange.Cells(1).Offset(n).Resize(h - n, 3).Delete shift:=xlShiftUp
            End If
    End With

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic
    
End If

Exit Sub
skip:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

 

[Sean15]

Hi:

Code in post #17 is really fast.
But something is off with column E.

Here is schedule when user enter 2 in G2. It's perfect.

PV of lease pymts-Updated VBA codes - V3.xlsm
	D	E	F	G	H
2			Number of Periods	2
3
4			Periods in	Months
5
6			Annual Rate	9.00%
7
8			Payment made at	End	of period
9
10
11		Period	Cash	Present Value
12		1	$237,651.19	$235,882.07
13		2	$237,651.19	$234,126.12
14
PV_Calc

Cell Formulas
Range	Formula
E12	E12	=IF($G$8="Beginning",0,1)
E13	E13	=E12+1
G12:G13	G12	=PV(IF($G$4="Months",$G$6/12,IF($G$4="Quarters",$G$6/4,IF($G$4="bi-annual",$G$6/2,$G$6))),E12,0,-F12,IF($G$8="Beginning",0,1))

Cells with Data Validation
Cell	Allow	Criteria
G2	Whole number	between 1 and 1200
G8	List	=Sheet1!$A$1:$A$2
G4	List	=Sheet1!$A$4:$A$7

But if user enter 10 in G2, schedule is:

PV of lease pymts-Updated VBA codes - V3.xlsm
	E	F	G	H
2		Number of Periods	10
3
4		Periods in	Months
5
6		Annual Rate	9.00%
7
8		Payment made at	End	of period
9
10
11	Period	Cash	Present Value
12	#VALUE!	$237,651.19	#VALUE!
13	#VALUE!	$237,651.19	#VALUE!
14	#VALUE!	$237,651.19	#VALUE!
15	#VALUE!	$237,651.19	#VALUE!
16	#VALUE!	$237,651.19	#VALUE!
17	#VALUE!	$237,651.19	#VALUE!
18	#VALUE!	$237,651.19	#VALUE!
19	#VALUE!	$237,651.19	#VALUE!
20	#VALUE!	$244,780.72	#VALUE!
21	#VALUE!	$244,780.72	#VALUE!
PV_Calc

Cell Formulas
Range	Formula
E12:E21	E12	=E11+1
G12:G21	G12	=PV(IF($G$4="Months",$G$6/12,IF($G$4="Quarters",$G$6/4,IF($G$4="bi-annual",$G$6/2,$G$6))),E12,0,-F12,IF($G$8="Beginning",0,1))

Cells with Data Validation
Cell	Allow	Criteria
G2	Whole number	between 1 and 1200
G8	List	=Sheet1!$A$1:$A$2
G4	List	=Sheet1!$A$4:$A$7

The formula in E12 is off. It should be: IF($G$8="Beginning",0,1) as shown in 1st screenshot of post #18. Formula in E12 must stay as is, it should not be changed or copied. Something in the VBA code is replacing formula.
Formula in E13 is E12+1
E14 is E13+1
E15 is E14+1 etc.,

I believe that's the confusion.
I sincerely apologise.

 

[Akuini]

The formula in E12 is off. It should be: IF($G$8="Beginning",0,1) as shown in 1st screenshot of post #18. Formula in E12 must stay as is, it should not be changed or copied. Something in the VBA code is replacing formula.

I can't reproduce the error, I entered any number in G2 & formula in E12 stayed the same.
Did you sort the table before running the code?
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[Sean15]

Please see link.

PV of lease pymts-Test.xlsm

Shared with Dropbox

www.dropbox.com