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


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?


This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of May 16, 2014.


For more resources for Microsoft Excel