Delete entire row, based on a table

**PingTing** · 07-30-2021, 12:09 PM

hi. I have table ([Table1]) which covers columns A:M (number of rows are dynamic). I have found code to delete the table rows, but I want to delete the entire sheet row, based on the table rows.

I want to delete all sheets rows in line with the table, except the first and last row (so I would be left with just the header row, the first row, and the last row).

Here's some code that would do the table rows.

For x = 1 to 5
Sheet1.ListObjects("Table1").ListRows(2).Delete
Next

I've attached a sample sheet. So in the sample, I would want to delete the entire sheet rows from 9 to 33 (this is dynamic of course) and leave just the header row (row 7), the first row (row 8), and the row called "dummy row".

**HSV** · 07-30-2021, 05:43 PM

If you are sure, change Select to Delete

Sub hsv()
 With Sheets("master")
  .Rows(.ListObjects(1).DataBodyRange(2, 1).Row).Resize(.ListObjects(1).ListRows.Count - 2).select 'Delete
 End With
End Sub

**PingTing** · 07-30-2021, 05:52 PM

@Harry.

Thank you so much. That worked perfectly. Really appreciate it.

**HSV** · 07-30-2021, 06:00 PM

Fine, you can also write it this way if you like.

With Sheets("master").ListObjects(1)
  .parent.Rows(.DataBodyRange(2, 1).Row).Resize(.ListRows.Count - 2).delete
 End With

Delete entire row, based on a table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete entire row, based on a table

Re: Delete entire row, based on a table

Re: Delete entire row, based on a table

Re: Delete entire row, based on a table

Thread Information

Users Browsing this Thread

Similar Threads

Delete entire row based on certain condition

[SOLVED] delete entire active cells row/s only if thet all inside of a table (whatever table it is)

[SOLVED] Can't delete entire row based on condition in table range.

[SOLVED] VBA to delete entire row based on condition met

[SOLVED] Delete entire row based on 2 criteria

Delete entire row based on criteria

Delete Entire Row based on criterion

Bookmarks

Bookmarks

Posting Permissions