Trying to Delete only Table Rows-VBA

[ESPN8]

Hello Everyone.

I am using a macro to import data from other sheets in my workbook to a table. I am then using another code to delete rows with zero in column "E". The problem I am having is that it is deleting the entire row from the sheet, not just the table row. I have other information I need to stay static next to the Table. I only want to delete the table row not the entire sheet row.

Current Code is below:

Sub DELETE_ZERO()

last = Cells(Rows.Count, "A").End(xlUp).Row 'Delete rows
For i = last To 2 Step -1
If (Cells(i, "E").Value) = 0 Then
Cells(i, "T").EntireRow.Delete
End If
Next i
End Sub



Any help would be appreciated.

Thank you!

 

[Akuini]

Try changing this:
Cells(i, "T").EntireRow.Delete
to this:
Cells(i, "T").Rows.Delete

 

[ESPN8]

Try changing this:
Cells(i, "T").EntireRow.Delete
to this:
Cells(i, "T").Rows.Delete

@Akuini I received the following error:


Run-time error '424':
Object required

 

[MickG]

Perhaps something like:-

ActiveSheet.ListObjects("Table1").DataBodyRange.Rows(3).Delete

 

[Akuini]

Hm, not sure why that happened. Which lines of code are highlighted yellow when the code stops?
If your original code doesn't throw an error then the new one shouldn't either.
T column is on the table, right?

 

[ESPN8]

@MickG error 9 subscript out of range
@Akuini Code worked fine before. When I change "EntireRow" to ROW I get that line highlighted with the error.

 

[MickG]

To utilize your code does this not work for you,
You may need to alter the "(n-1) depending on whether or not your table has headers.

[COLOR="Navy"]Sub[/COLOR] MG13Aug43
[COLOR="Navy"]Dim[/COLOR] Last [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Last = Cells(Rows.Count, "A").End(xlUp).Row

[COLOR="Navy"]For[/COLOR] i = Last To 2 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]If[/COLOR] (Cells(i, "E").Value) = 0 [COLOR="Navy"]Then[/COLOR]
    ActiveSheet.ListObjects("Table1").DataBodyRange.Rows(i - 1).Delete
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ESPN8]

@MickG Thank you! It seems to be working fine now. My table does have headers,but no change to your code was needed. I couldn't find any issues with my headers.

 

[Akuini]

@Akuini Code worked fine before. When I change "EntireRow" to ROW I get that line highlighted with the error.

Not ROW it should be ROWS.

 

[MickG]

You're welcome