Auto add/delete rows based on specific cell value.

Sean15

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
719
Office Version
  1. 2010
Platform
  1. Windows
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
EFG
1
2Number of Periods51
3
4
5
6Annual Rate9.00%
7
8
9PeriodCashPresent Value
101$237,651.19$235,882.07
112$237,651.19$234,126.12
123$237,651.19$232,383.25
134$237,651.19$230,653.35
145$237,651.19$228,936.33
156$237,651.19$227,232.09
167$237,651.19$225,540.53
178$237,651.19$223,861.57
189$244,780.72$228,860.96
1910$244,780.72$227,157.28
2011$244,780.72$225,466.28
2112$244,780.72$223,787.87
2213$244,780.72$222,121.96
2314$244,780.72$220,468.45
2415$244,780.72$218,827.24
2516$244,780.72$217,198.26
2617$244,780.72$215,581.39
2718$244,780.72$213,976.57
2819$244,780.72$212,383.69
2920$244,780.72$210,802.67
3021$252,124.14$215,510.42
3122$252,124.14$213,906.13
3223$252,124.14$212,313.78
3324$252,124.14$210,733.28
3425$252,124.14$209,164.54
3526$252,124.14$207,607.49
3627$252,124.14$206,062.02
3728$252,124.14$204,528.06
3829$252,124.14$203,005.52
3930$252,124.14$201,494.31
4031$252,124.14$199,994.35
4132$252,124.14$198,505.56
4233$259,687.87$202,938.69
4334$259,687.87$201,427.98
4435$259,687.87$199,928.52
4536$259,687.87$198,440.21
4637$259,687.87$196,962.99
4738$259,687.87$195,496.77
4839$259,687.87$194,041.45
4940$259,687.87$192,596.98
5041$259,687.87$191,163.25
5142$259,687.87$189,740.20
5243$259,687.87$188,327.74
5344$259,687.87$186,925.80
5445$267,478.50$191,100.32
5546$267,478.50$189,677.74
5647$267,478.50$188,265.74
5748$267,478.50$186,864.26
5849$267,478.50$185,473.21
5950$267,478.50$184,092.52
6051$267,478.50$182,722.10
61Total$12,852,671.76$10,604,259.89
PV_Calc
Cell Formulas
RangeFormula
E11:E59E11=IF($G$2>E10,E10+1,"")
E60E60=IF($G$2>=E59,E59+1,"")
G10:G60G10=IF(OR(E10="",F10=""),"",PV($G$6/12,E10,0,-F10,0))
F61F61=SUBTOTAL(109,[Cash])
G61G61=SUBTOTAL(109,[Present Value])
Cells with Data Validation
CellAllowCriteria
G2Whole numberbetween 1 and 1200
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi:

I found some codes that resizes table:

VBA Code: 
Sub ResizeTable()

Dim rng As Range
Dim tbl As ListObject

  Set rng = Range("Table1[#All]").Resize(Range("G2").Value)
  Set tbl = ActiveSheet.ListObjects("Table1")
  tbl.Resize rng
End Sub

But the codes need some work. If I resize table in post #1 to 30, unwanted rows are not deleted. Also, the subtotals in F29 and G29 should be one row below the actual table size, in this example row F41 and G41.

PV of lease pymts.xlsm
EFG
1
2Number of Periods30
3
4
5
6Annual Rate9.00%
7
8
9PeriodCashPresent Value
101$237,651.19$235,882.07
112$237,651.19$234,126.12
123$237,651.19$232,383.25
134$237,651.19$230,653.35
145$237,651.19$228,936.33
156$237,651.19$227,232.09
167$237,651.19$225,540.53
178$237,651.19$223,861.57
189$244,780.72$228,860.96
1910$244,780.72$227,157.28
2011$244,780.72$225,466.28
2112$244,780.72$223,787.87
2213$244,780.72$222,121.96
2314$244,780.72$220,468.45
2415$244,780.72$218,827.24
2516$244,780.72$217,198.26
2617$244,780.72$215,581.39
2718$244,780.72$213,976.57
2819$244,780.72$212,383.69
29Total$4,593,797.41$4,264,445.27
3020$244,780.72$210,802.67
3121$252,124.14$215,510.42
3222$252,124.14$213,906.13
3323$252,124.14$212,313.78
3424$252,124.14$210,733.28
3525$252,124.14$209,164.54
3626$252,124.14$207,607.49
3727$252,124.14$206,062.02
3828$252,124.14$204,528.06
3929$252,124.14$203,005.52
4030$252,124.14$201,494.31
41 $252,124.14 
42 $252,124.14 
43 $259,687.87 
44 $259,687.87 
45 $259,687.87 
46 $259,687.87 
47 $259,687.87 
48 $259,687.87 
49 $259,687.87 
50 $259,687.87 
51 $259,687.87 
52 $259,687.87 
53 $259,687.87 
54 $259,687.87 
55 $267,478.50 
56 $267,478.50 
57 $267,478.50 
58 $267,478.50 
59 $267,478.50 
60 $267,478.50 
61 $267,478.50 
PV_Calc
Cell Formulas
RangeFormula
E31:E60,E11:E28E11=IF($G$2>E10,E10+1,"")
F29F29=SUBTOTAL(109,[Cash])
E30E30=IF($G$2>E28,E28+1,"")
E61E61=IF($G$2>=E60,E60+1,"")
G30:G61,G10:G28G10=IF(OR(E10="",F10=""),"",PV($G$6/12,E10,0,-F10,0))
G29G29=SUBTOTAL(109,[Present Value])
Cells with Data Validation
CellAllowCriteria
G2Whole numberbetween 1 and 1200
 
Upvote 0
Try this:
I removed the formula in column E, it's just a sequence of numbers, correct?
Put the code in the sheet code module, this is how:
Copy the code > open the sheet > Right-click the sheet tab > View Code > paste the code
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Address = "$G$2" Then
    Dim n As Long, i As Long, h As Long
    
    n = Target.Value
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManual
    
    With ActiveSheet.ListObjects(1)
           h = .DataBodyRange.Rows.Count
            If h < n Then
                For i = 1 To n - h
                    .ListRows.Add AlwaysInsert:=True
                Next
                .DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange.Columns(1), xlFillSeries
            ElseIf h > n Then
                For i = h To n + 1 Step -1
                    .ListRows(i).Delete
                Next
            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
 
Upvote 0
Solution
Hi:

Thank you very much for your help.
In View Code, I get three code windows.
In which window should I paste the code?

The sequence of numbers in column E is necessary to calculate present value.


Grateful,
Sean
 

Attachments

  • Screenshot 2023-08-12 202003 MrExcel.png
    Screenshot 2023-08-12 202003 MrExcel.png
    47.2 KB · Views: 5
Last edited:
Upvote 0
Sean15 said:
In View Code, I get three code windows.
In which window should I paste the code?
Click to expand...
Actually, that's just one code window with 3 Subs.
Just paste the code below the existing code (as seen in your screenshot).
 
Upvote 0
Hi:

The code in post #4 works wonderfully.
Thank you so much.

Sean
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
Hi:

What part of the code below inserts the sequence of numbers in column E?



Akuini said:
VBA Code:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Address = "$G$2" Then
    Dim n As Long, i As Long, h As Long
   
    n = Target.Value
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManual
   
    With ActiveSheet.ListObjects(1)
           h = .DataBodyRange.Rows.Count
            If h < n Then
                For i = 1 To n - h
                    .ListRows.Add AlwaysInsert:=True
                Next
                .DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange.Columns(1), xlFillSeries
            ElseIf h > n Then
                For i = h To n + 1 Step -1
                    .ListRows(i).Delete
                Next
            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
Click to expand...
 
Upvote 0
Sean15 said:
What part of the code below inserts the sequence of numbers in column E?
Click to expand...
This part:
VBA Code: 
.DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange.Columns(1), xlFillSeries
the codes assumes that the first cell in the table (i.e. E10) already has value which is 1, so the sequence will be 1,2,3...etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
SUMIF and AVERAGEIF two separate columns, only picking the greater value between the two column.
Replies
5
Views
281
hagia_sofia
hagia_sofia
E
  • Solved
extend Excel formula to filter based on a period
Replies
5
Views
256
smozgur
smozgur
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
233
arun_eagertolearn
A
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
235
Joe4
Joe4
JenniferMurphy
  • Question Question
Is there a Null function?
Replies
6
Views
456
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top