Adding or Subtracting Rows in a Table Based on a Cell Value

[ashperson95]

Okay, I am trying to create a program that creates a table, and then if the value of a certain cell is larger or smaller than the amount of rows already in the table (the table has a standard 10 rows with data), then it adds or subtracts based on that value. I'm not very familiar with VBA language, but I do have some background in Java programming (although it's been several years). I've only gotten to the adding rows part before I came up with an error message and I can't figure out what I did wrong.

Here is what I have so far:

Sub CreateTable()
'Creating a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$7:$B$17"), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub


Sub EditRows()
Dim rows As Long
Dim Life As Long
'Gets value of Life Expectancy
Life = Worksheets("Sheet1").Cells(2, "D").Value
'Counts rows in table
rows = Range("Table1").rows.Count


If Life > rows Then Data.ListObjects("Table1").ListRows.Add
'This line gives me a 424 Object Required message
End Sub

Like I said, I am unfamiliar with VBA language, so is my syntax wrong or am I just completely wrong?

 

[ashperson95]

I figured it out, I didn't realize that the (1) you posted in your reply was a row position. Here's what I did:

Sub CreateTable()'Creating a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$7:$B$17"), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub


Sub EditRows()
Dim rows As Long
Dim Life As Long
'Gets value of Life Expectancy
Life = Worksheets("Sheet1").Cells(2, "D").Value
'Counts rows in table
rows = Range("Table1").rows.Count


'Sees if Life Expectancy is higher than the amount of rows, and adds rows accordingly
If Life > rows Then
Do Until Life = rows
ListObjects("Table1").ListRows.Add
AlwaysInsert = True
rows = Range("Table1").rows.Count
Loop
End If


'Sees if Life Expectancy is lower than the amount of rows, and deletes rows accordingly
If Life < rows Then
Do Until Life = rows
ActiveSheet.ListObjects("Table1").ListRows(rows).Delete
rows = Range("Table1").rows.Count
Loop
End If
End Sub

Also, sorry for not using code tags. And thank you for your help!

 

[ashperson95]

Well, except now I need to continue the data that was already in the table into the new empty rows. I've searched online already and really can't find anything. Does anyone have any suggestions?

 

[Andrew Poulsom]

What do you mean by "I need to continue the data that was already in the table into the new empty rows" exactly?

 

[ashperson95]

So, here is what my sample table looks like:

Year Savings
1 20
2 22
3 24
4 26
5 28
6 30
7 32
8 34
9 36
10 38

And if the Life Expectancy is 15 years, my code will add five more rows to this table. I want the data to populate the new rows with a continuation of the years (so up to 15 years) and the savings (which has a formula in it to add to the previous row).

**EDIT** Also, I apologize for creating a new thread. I thought that since the subject of my issue had shifted that I was supposed make a new one.

 

[ashperson95]

Bumping this up, I still can't figure it out.

 

[Andrew Poulsom]

Try:

Sub FillTable()
    Dim MyTable As ListObject
    Dim Used As Range
    With ActiveSheet
        Set MyTable = .ListObjects("table1")
        Set Used = .Range("A2:A" & .Range("A1").End(xlDown).Row).Resize(, 2)
        If MyTable.ListRows.Count > Used.Rows.Count Then
            Used.AutoFill MyTable.DataBodyRange
        End If
    End With
End Sub

And please be more patient.

 

[ashperson95]

I changed the range values to the range of my table. If I did that incorrectly, let me know. Also, it is not filling in the values.

Sub FillTable()    Dim MyTable As ListObject
    Dim Used As Range
    With ActiveSheet
        Set MyTable = .ListObjects("Table1")
        Set Used = .Range("A8:A17" & .Range("B8:B17").End(xlDown).Row).Resize(, 2)
        If MyTable.ListRows.Count > Used.rows.Count Then
            Used.AutoFill MyTable.DataBodyRange
        End If
    End With
End Sub

 

[Andrew Poulsom]

Try:

Sub FillTable()
    Dim MyTable As ListObject
    Dim Used As Range
    With ActiveSheet
        Set MyTable = .ListObjects("Table1")
        Set Used = .Range("A8:A" & .Range("A7").End(xlDown).Row).Resize(, 2)
        If MyTable.ListRows.Count > Used.Rows.Count Then
            Used.AutoFill MyTable.DataBodyRange
        End If
    End With
End Sub

 

[ashperson95]

That fills in the correct years, but it doesn't fill in the savings correctly. If you refer to the sample table I posted, it started those values over again (so the newly filled in row started at 20 instead of 40).

 

[Andrew Poulsom]

This is what I got with original data down to row 17:

Excel 2010
	A	B
7	Year	Savings
8	1	20
9	2	22
10	3	24
11	4	26
12	5	28
13	6	30
14	7	32
15	8	34
16	9	36
17	10	38
18	11	40
19	12	42
20	13	44
21	14	46
22	15	48
Sheet1