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

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 16, 2014

• #2

Welcome to MrExcel.

You haven't assigned anything to the variable Data. Try replacing it with ActiveSheet.

 

Upvote 0

Dreadknight Nasus

Board Regular

Joined

Jun 3, 2013

Messages

241

• May 16, 2014

• #3

Just get rid of the 'Data.' and it should work.

Also, are you trying to loop it, or just add a row once?

 

Upvote 0

A

ashperson95

New Member

Joined

May 16, 2014

Messages

36

• May 16, 2014

• #4

That worked, thank you! I know that probably seemed like a stupid question.
I'll most likely be posting later as I move on into doing this function in a loop until all needed rows have been added, and then filling in the data in the new rows

 

Upvote 0

A

ashperson95

New Member

Joined

May 16, 2014

Messages

36

• May 16, 2014

• #5

Dreadknight, I will be trying to loop it, I wanted to get the adding rows function first. I'm fairly new to VBA language so I'm doing everything in tiny steps

 

Upvote 0

A

ashperson95

New Member

Joined

May 16, 2014

Messages

36

• May 16, 2014

• #6

Ugh, okay, so I've run into another problem.
I thought deleting a row would be similar to adding one, but I've gotten the syntax wrong again. I tried googling it and the way I currently have it is the only way I can find, but I'm still getting error messages.

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


Do Until Life = rows
If Life > rows Then ListObjects("Table1").ListRows.Add
AlwaysInsert = True
If Life < rows Then ActiveSheet.ListObjects("Table1").ListRows.Delete
'Gives me runtime error 438
rows = Range("Table1").rows.Count
Loop
End Sub

Click to expand...

Help please!

 

Upvote 0

A

ashperson95

New Member

Joined

May 16, 2014

Messages

36

• May 16, 2014

• #7

Bumping this because I really need help, please!

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 16, 2014

• #8

The ListRows object doesn't have a Delete method. You need to provide it with an Index to return a ListRow object, eg:

Rich (BB code):

ActiveSheet.ListObjects("Table1").ListRows(1).Delete

 

Upvote 0

A

ashperson95

New Member

Joined

May 16, 2014

Messages

36

• May 16, 2014

• #9

It doesn't give me the error message anymore, but it's not deleting the rows.

I've changed it a little so that the adding and deleting functions were in different loops. Here:

Code:

 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
Do Until Life = rows
ListObjects("Table1").ListRows.Add
AlwaysInsert = True
rows = Range("Table1").rows.Count
Loop
End If


If Life < rows Then
Do Until Life = rows
ActiveSheet.ListObjects("Table1").ListRows(1).Delete
rows = Range("Table1").rows.Count
Loop
End If
End Sub

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 16, 2014

• #10

Your code doesn't compile for me. Please use Code tags in future postings.

 

Upvote 0